MySQL - Set a column name to the ID on creation?

I'm trying to implement a custom sort field for a list of records. When I create a new record, by default I would like this field to match the ID number of that record. Is there any way to achieve this without having to perform two queries?

Any advice appreciated.

Tha开发者_运维知识库nks

You can get the next auto-increment id by using

SHOW TABLE STATUS FROM tablename LIKE Auto_increment
/*or*/
SELECT `auto_increment` FROM `INFORMATION_SCHEMA.TABLES` WHERE table_name = 'tablename'

This will give you the next auto_increment value.

Then make a before insert trigger:

DELIMITER $$

CREATE TRIGGER bi_table1_each BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
  DECLARE next_id integer;
  SELECT `auto-increment` FROM `INFORMATION_SCHEMA.TABLES` INTO Next_id 
  WHERE TABLE_NAME = 'table1' LIMIT 1;     
  SET new.sortcolumn = next_id;
END $$

DELIMITER ;

Links
http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

I think you can leave this field equal to NULL by default and then in your query do this:

ORDER BY ifnull(sort_field, id)

Set it to null or something like that by default and make your query sort by id if the sort field is null. PseudoSQL:

SELECT blah, IFNULL(t.sort, t.id) AS sortval
FROM t
ORDER BY sortval

You can use MAX(ID) expression to be sure that your next INSERT will be the greater id + 1 :

INSERT INTO table_name (..., sort_field) VALUES (..., MAX(id)+1)

Hope this helps, bye!