Set column value of this/next auto_increment value

Question

Is there any way to accomplish below sql result as a one liner.

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 |
+----+------+

Yes, I know the other way is

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

I suggest that you don't do it at all. Names that really do end in numbers will cause confusion. You can always select the concatenated result when you wish. If you want to do it often enough, create a function. — Dan Bracuk, Dec 11 '15 at 18:10
Well there is a way but i also think that @DanBracuk is right you shouldn't do it. The way is to select the auto_increment value from schema_information and concatenate. See it here: http://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table — Jorge Campos, Dec 11 '15 at 18:24
@DanBracuk to expand by situation. My scenario comes from the need to create temporary user when (http) request comes. Then we have cron job which runs every hour and deletes temporary users. I solved it like this: `begin transaction; insert into users (username) values (replace(replace(replace(concat(sysdate(6), rand(7)), ' ', '-'), ':', '-'), '.', '-')) update user set username = concat(usertemp, id) where id = user_id_of_upper_query; commit;` Username as obvious is unique (unique index column). Data after just first query, only username: `2015-12-11-19-52-15-3540820-9065021936842261` — broadband, Dec 11 '15 at 19:04
@JorgeCampos thnx, but what about race condition when reading Id from schema_information. For example if two queries read the same value, and if unique index is on the column you are inserting there could be a problem. I took solution in upper comment. — broadband, Dec 11 '15 at 19:05
There are better ways to identify someone as a temporary user. A field in the table identifying the type of user springs to mind. — Dan Bracuk, Dec 11 '15 at 19:10
@DanBracuk ok, you can add new column type enum ('standard', 'temporary', ...) or something like this, but how would you solve creating new unique username. The thing is that username doesn't register. It is a requirement that we create it and afterwards delete it after n hours. — broadband, Dec 11 '15 at 19:28

score 2 · Accepted Answer · answered Dec 11 '15 at 22:07

An option or approach can be via a Virtual (Computed) Columns.

Example:

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)

Set column value of this/next auto_increment value

1 Answers1