MySQL - 在创建时将列名设置为 ID? [英] MySQL - Set a column name to the ID on creation?

问题描述

我正在尝试为记录列表实现自定义排序字段.创建新记录时，默认情况下我希望此字段与该记录的 ID 号匹配.有没有什么办法可以不用执行两次查询来实现这一点?

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?

感谢任何建议.

谢谢

推荐答案

您可以使用

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

这将为您提供下一个 auto_increment 值.

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 ;

链接
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

这篇关于MySQL - 在创建时将列名设置为 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！