Two autoincrements columns or autoincrement and same value in other column

Question

I need two columns in table that would have same value on insert. Is there any way to do it from database side?

Answer 1

So you want to let one column use the auto_increment feature, but make another column in the same table also have the same value?

I can't think of a reason you would need this feature. Perhaps you could explain what you're trying to accomplish, and I can suggest a different solution?

A trigger won't work for this. It's a chicken-and-egg problem:

• You can't change any column's value in an AFTER trigger.
• But the auto-increment value isn't set yet when a BEFORE trigger executes.

It also won't work to use a MySQL 5.7 GENERATED column:

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT GENERATED ALWAYS AS (id)
);

ERROR 3109 (HY000): Generated column 'why_would_you_want_this' 
cannot refer to auto-increment column.

You can't do it in a single SQL statement. You have to INSERT the row, and then immediately do an UPDATE to set your second column to the same value.

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT
);

INSERT INTO MyTable () VALUES ();

UPDATE MyTable SET why_would_you_want_this = LAST_INSERT_ID() 
WHERE id = LAST_INSERT_ID();

You could alternatively generate the ID value using some other mechanism besides AUTO_INCREMENT (for example a Memcached incrementing key). Then you could insert the new value in both columns:

INSERT INTO MyTable (id, why_would_you_want_this) VALUES ($gen_id, $gen_id);

Answer 2

Define a before or after insert trigger and assign the value of the 2nd field in the trigger.

If the 1st field is an auto increment column, then you need to use an after insert trigger. If your application assigns value to the 1st field, then you can use a before insert trigger.

However, I would no necessarily duplicate the value on insert. You can leave the 2nd field as null on insert, which would mean that its value is the same as the 1st field's. The only drawback of this approach is that it may be more difficult to create joins on the 2nd field.

Answer 3

You can do this in one query by using the primary key (assumed to be id) and setting your column (assumed to be columnName):

"INSERT INTO tableName SET `columnName` = (SELECT MAX(x.id) FROM tableName x)+1"

This will not work if you have deleted the most recent primary key row however. To get past this, you can insert into the id as well:

"INSERT INTO tableName SET `columnName` = (SELECT MAX(x.id) FROM tableName x)+1, `id`= (SELECT MAX(x.id) FROM tableName x)+1" 

However, this solution has the downside (or upside depending on the case) of reusing primary key values that have already been deleted.

suggested way:
To use the actual auto_increment value, you can do this:

"INSERT INTO tableName SET `columnName` = (SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name')"

Sources that helped me solve this:
Prashant Pimpale's answer