mysql默认值是另一个字段的值,MySQL默认值作为其他字段的值

Can I, and, if I can, how can I set the default value of a field in a MySQL table to the value of another field?

Thing is: I have data, and each data object has its ID in the table. But, I would like the possibility to rearrange the data, changing their sorting index, without altering their ID. Thus, the field sort_num should by default be set to the value given to the auto-incremented indexed field ID.

Thanks in advance!

解决方案

I see two possible solutions for this:

1. Possibility:

You use a function to simply ignore sort_num if it is not set:

`SELECT * FROM mytable ORDER BY coalesce(sort_num, id)`

coalesce() returns the first non-null value, therefore you would insert values for sort_num if you really need to reorder items.

2. Possibility:

You write a trigger, which automatically sets the value if it is not set in the insert statement:

DELIMITER //

CREATE TRIGGER sort_num_trigger

BEFORE INSERT ON mytable

FOR EACH ROW BEGIN

DECLARE auto_inc INT;

IF (NEW.sort_num is null) THEN

-- determine next auto_increment value

SELECT AUTO_INCREMENT INTO auto_inc FROM information_schema.TABLES

WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = 'mytable';

-- and set the sort value to the same as the PK

SET NEW.sort_num = auto_inc;

END IF;

END

//

(inspired by this comment)

However, this might run into parallelization issues (multiple queries inserting at the same time)