Mysql – Default value of a column based on other column in MySQL

MySQL

I have a mytable table in my MySQL database.

It contains the following columns:

  • id (INT) PRIMARY KEY – AUTO INCREMENT – DEFAULT NONE
  • name (VARCHAR (220)) – DEFAULT NULL
  • code (VARCHAR (220)) – DEFAULT NULL

Is it possible that if I insert something in this table I want the code column to autogenerate its value based on the columns id and name.

If I tried to run this example query:

INSERT INTO mytable(name) VALUES ('myname');

I want to get the following result:

+----+--------+-------+
| id | name   | code  |
+----+--------+-------+
| 1  | myname | my1   |
+----+--------+-------+

The code column default value must be the first 2 characters of myname which is my plus the id column.

I can actually do this using PHP code but I think there must be a way using MySQL only.

Best Answer

If the code field values are altered never the solution is trivial - look on the fiddle provided in Sam's comment. The only point I'd recommend to specify VIRTUAL attribute for calculated field explicitely.

If the code field values specified in a question are default only, and they can be altered from the default value freely either when inserting or by data updating, I'd recommend to make this field NULLable and use NULL value as a mark that the value is default one and must be calculated in a query. In all places where you need the value of this field you will use the below expression instead of direct field's value selection:

SELECT  ..., 
        COALESCE(code, CONCAT(SUBSTRING(name,1,2),id)) code, 
        ...
Related Question