How to Assign Usernames with Concatenated Primary Key Values in MySQL?

Inserting Data with Concatenated String and Primary Key

Insertions into a MySQL table involve assigning values to columns, often including an auto-incremented primary key. However, sometimes one might desire to concatenate a string with the primary key while inserting, as in the case of assigning usernames as "user" followed by the primary key's value.

Challenge:

The user table contains several columns, including an auto-incremented primary key "id" and a "username" column. The intent is to automatically assign usernames as "user" appended with the respective primary key values.

Approach:

• Using a single SQL statement is impossible because the primary key value is only generated after executing the "BEFORE INSERT" trigger, and subsequent modification of "username" is prohibited in the "AFTER INSERT" trigger.
• A viable workaround is to perform the insertion first and then execute an immediate update to concatenate the primary key value with the string.

Alternative:

• If the primary key is not auto-incremented and instead specified, the concatenation operation can be performed directly in the PHP code before passing values as parameters.

Note:

• MySQL 5.7's generated columns cannot accommodate this scenario. Attempted creation of a generated column referencing an auto-incremented column results in the error "Generated column 'username' cannot refer to auto-increment column."

The above is the detailed content of How to Assign Usernames with Concatenated Primary Key Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!