mysql - 设置此/下一个自动增量值的列值

Question

有没有什么办法可以将下面的sql结果作为一个行来完成。

create table test( id int not null primary key auto_increment, name char(10));

insert into test (name) values ('voda'+ this_value_of_id);

// so select would return
MariaDB [testdb]> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | foo1 |
+----+------+

是的，我知道另一种方式是

begin transaction;
insert into test (name) values ('voda');
update test set name = concat('voda', id) where id = 1;
commit;

Answer 1

选项或方法可以通过 Virtual (Computed) Columns .

示例:

MariaDB [testdb]> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> CREATE TABLE `test` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `name` CHAR(10),
    ->   `nameid` VARCHAR(20) AS (CONCAT(`name`, `id`)) VIRTUAL
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> INSERT INTO `test`
    ->   (`name`)
    -> VALUES
    ->   ('foo'), ('voda');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT
    ->   `id`,
    ->   `nameid` `name`
    -> FROM
    ->   `test`;
+----+-------+
| id | name  |
+----+-------+
|  1 | foo1  |
|  2 | voda2 |
+----+-------+
2 rows in set (0.00 sec)