mysql, set value of column "id"(auto increment ) as an default value for column B

I am designing a message system.

In the table where I store the messages, the message_id column is auto increment.

I have another column message_r开发者_如何学Goeference_id. I want its default value to be message_id.

How do I set this?

You can't set the default value to the value in another column. Instead if your column is NOT NULL, you could change it to nullable and set the default value to NULL. Then when you select it, use this:

SELECT IFNULL(message_reference_id, message_id) AS message_reference_id
FROM your_table

You could even put this in a view.

Alternatively you can create a trigger which updates the value on insert.

You could use NULL instead and, on access, use COALESCE(message_reference_id, message_id).

This gives you message_reference_id in the general case. However, if it is NULL, you get message_id instead.

EDIT: With this knowledge, you can work with inner and outer queries - e.g. use the COALESCE trick in the inner query and the GROUP BY in the outer one.

But I don't know how well this performs, as the GROUP BY would probably like to use indexes, which cannot be provided this way.