将一种自动增量列添加到mysql表中 [英] adding a kind of auto increment column to a mysql table

问题描述

我想在mysql表中添加一列，其中的单元格将获得如下值:

I want to add a column to a mysql table where its cells will get values like:

 newColumn
 -----------
 log-00001
 log-00002
 log-00003
 ....

值log-0000x将由mysql自动创建.这就像自动递增"列，但带有"log-"前缀.这可能吗?谢谢

the values log-0000x will automatically be created by mysql. This is like an "auto incremented" column but with the 'log-' prefix. Is this possible? Thx

推荐答案

MySQL不会自动增加除整数以外的任何内容.您不能自动增加字符串.

MySQL doesn't auto-increment anything other than integers. You can't auto-increment a string.

您不能使用触发器基于自动增量值来填充字符串.原因是在执行之前"触发器时尚未生成自动增量值，现在更改之后"触发器中的列为时已晚.

You can't use a trigger to populate a string based on the auto-increment value. The reason is that the auto-increment value isn't generated yet at the time "before" triggers execute, and it's too late to change columns in "after" triggers.

另请参阅对 https://stackoverflow.com/a/26899091/20860

出于相同的原因，您不能使用虚拟列.

You can't use a virtual column, probably for the same reason.

mysql> create table t (id int(5) zerofill auto_increment primary key, 
    virtcolumn char(8) as (concat('log-', id)));
ERROR 3109 (HY000): Generated column 'virtcolumn' cannot refer to auto-increment column.

您必须让整数自动递增，然后在插入完成后随后使用UPDATE填充"log-nnnnnn"字符串.

You'll have to let the integer auto-increment, and then subsequently use UPDATE to populate your "log-nnnnnn" string after the insert is done.

CREATE TABLE `t` (
  `id` int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `log` char(9) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `t` () VALUES ();

UPDATE `t` SET `log` = CONCAT('log-', `id`) WHERE `id` = LAST_INSERT_ID();

SELECT * FROM `t`;
+-------+-----------+
| id    | log       |
+-------+-----------+
| 00001 | log-00001 |
+-------+-----------+

这篇关于将一种自动增量列添加到mysql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！