auto increment second column

Question

I have a table with 2 columns. The ID column auto increments. I'm trying to auto increment the user column with the same ID as the id column, but with a "user" prefix (example: user100, where the ID is also 100) basically just like what is done on stackoverflow.

CREATE TABLE test_table (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     user CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

Is there a way of doing this in 1 query? Instead of inserting into the DB, then querying to get the ID, and inserting the ID into the user column?

why do you need to duplicate it (and it **is** duplicating)? — Marko D, Mar 13 '13 at 14:28
@DarylGill SO does not use 'Apache' it uses IIS7. http://meta.stackexchange.com/questions/10369/which-tools-and-technologies-are-used-to-build-the-stack-exchange-network — ShuklaSannidhya, Mar 13 '13 at 14:33
Check this http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html — Voitcus, Mar 13 '13 at 14:34
i just want to pre set usernames just like how stackoverflow does it. like my username is user2035819 so the ID in my DB should be 2035819 — srakl, Mar 13 '13 at 14:35
You can't have more than ONE auto_increment column in a table. MySQL simply does not support this. But you can use a trigger to accomplish what you want... but why would you want to duplicate data like this? You'd be wasting space by doubling up on the id in two fields, and you can trivially do the concatentation at select time with `select concat('user', id) as userid` — Marc B, Mar 13 '13 at 14:35
@user2035819 you are doing it the wrong way then, you don't need to duplicate the id — Marko D, Mar 13 '13 at 14:35
http://stackoverflow.com/questions/5972446/insert-autoincrement-into-second-column — Matt, Mar 13 '13 at 14:35

Jocelyn · Answer 1 · 2013-03-13T15:38:15.560

2

Use a BEFORE trigger:

DELIMITER $$
CREATE TRIGGER test_table_trigger
BEFORE INSERT ON test_table
FOR EACH ROW BEGIN
    SET NEW.`user` = CONCAT(NEW.`user`, NEW.id);
END $$
DELIMITER ;

Documentation: MySQL triggers

edited Mar 13 '13 at 15:38

answered Mar 13 '13 at 14:46

Jocelyn

11,209
10
43
60

im getting this error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 – srakl Mar 13 '13 at 14:55
"user" seems to be a reserved word. I updated the code (I added backticks around "user"). – Jocelyn Mar 13 '13 at 15:22
hmm, still same error – srakl Mar 13 '13 at 15:33
I checked it in phpmyadmin, it works fine. Copy and paste the **whole** code in phpmyadmin. – Jocelyn Mar 13 '13 at 15:38
what version of mysql are u running? on my xampp on my mac its version 5.1.44 – srakl Mar 13 '13 at 15:59
My mysql version is 5.5.21. But it should not matter, as the syntax of DELIMITER, CREATE TRIGGER and CONCAT is valid in version 5.1.x too. Did you copy and paste in phpmyadmin all the code in the gray box in my answer? Otherwise, describe what you are doing. – Jocelyn Mar 13 '13 at 17:00
i guess its the version then. cause just noticed this error. #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' – srakl Mar 14 '13 at 03:10

Mike Mackintosh · Answer 2 · 2017-02-17T23:21:57.353

1

You can do a trigger

Before Trigger:

mysql> truncate table test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER test_table_trigger
    -> BEFORE insert ON test_table
    -> FOR EACH ROW
    -> BEGIN
    ->    SET new.user = CONCAT('user', (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table'));
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> INSERT INTO test_table values ();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_table values ();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_table values ();
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * FROM test_table;
+----+-------+
| id | user  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
+----+-------+
3 rows in set (0.00 sec)

The above should then use the auto-increment after it's designated to the id column and append it to the string user. The auto increment ID is pulled from Information_Schema, as if this is in a transaction or many queries, it could be set wrong.

edited Feb 17 '17 at 23:21

answered Mar 13 '13 at 14:33

Mike Mackintosh

13,917
6
60
87

shouldn't __AFTER__ INSERT be UPDATE not INSERT again? – Waygood Mar 13 '13 at 14:35
This will cause a "duplicate key" error, because the trigger will attempt to insert another record with the same id. – Jocelyn Mar 13 '13 at 14:36
Updated it to before, using LAST_INSERT_ID() which is defined during the before stage. – Mike Mackintosh Mar 13 '13 at 14:36
This is not how BEFORE triggers work, you must not write the INSERT in the trigger. – Jocelyn Mar 13 '13 at 14:38
QUOTE: __LAST_INSERT_ID() representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement__ (which may not be this table). Change it back and modify it to update – Waygood Mar 13 '13 at 14:42
1

@Waygood: there is no need to use UPDATE. Just use BEFORE triggers properly, [as described in the documentation](http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html) with keyword "SET" to change the value of "user" just before the insert. There is no need to use LAST_INSERT_ID() since the id value is stored in NEW.id – Jocelyn Mar 13 '13 at 14:45
The previous edit of this used NEW.id, but SET would be a better solution +1 – Waygood Mar 13 '13 at 14:47
Updated. The above has the best performance out of some of the other options I've played with – Mike Mackintosh Mar 13 '13 at 16:28

score 0 · Answer 3 · answered Mar 13 '13 at 14:45

0

Maybe you can try this, picking up last inserted id and concatenating string with converted value:

INSERT INTO test_table (user) VALUES ('user')

UPDATE test_table
SET user = user + CAST(LAST_INSERT_ID() AS VARCHAR)
WHERE id = LAST_INSERT_ID()

answered Mar 13 '13 at 14:45

veljasije

6,722
12
48
79

As long as these statements are executed together on the same connection this is safe for concurrency. – cfeduke Mar 13 '13 at 14:53
thats 2 queries. can it be done in 1? – srakl Mar 13 '13 at 14:53
Maybe you can use MAX(id) in subquery, in INSERT statement, assuming that data wasn't deleted and there is no gap between ID's – veljasije Mar 13 '13 at 14:58

auto increment second column

3 Answers3

Linked