Adding some sort of auto increment column to mysql table
I want to add a column to mysql table where its cells will get values like:
newColumn
-----------
log-00001
log-00002
log-00003
....
the values log-0000x will be automatically generated by mysql. This is similar to the "auto incremented" column, but with the "log-" prefix. Is it possible? thank
source to share
MySQL does not automatically add anything other than integers. You cannot automatically increment a row.
You cannot use a trigger to fill a row based on an auto increment value. This is because the autoincrement value is not yet generated at runtime "before" triggers, and it is too late to change columns in triggers "after".
See also my answer at fooobar.com/questions/2418318 / ...
You cannot use a virtual column, possibly 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.
You will need to specify an auto-increment integer value and then use UPDATE to populate your "log-nnnnnn" line after the insert is complete.
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 |
+-------+-----------+
source to share