Adding A Kind Of Auto Increment Column To A Mysql Table

I want to add a column to a mysql table where its cells will get values like: newColumn ----------- log-00001 log-00002 log-00003 .... the values log-0000x will automatica

Solution 1:

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.

See also my answer to https://stackoverflow.com/a/26899091/20860

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

mysql>createtable 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.

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.

CREATETABLE `t` (
  `id` int(5) unsigned zerofill NOTNULL AUTO_INCREMENT,
  `log` char(9) DEFAULTNULL,
  PRIMARY KEY (`id`)
);

INSERTINTO `t` () VALUES ();

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

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