Concatenating a string and primary key Id while inserting

I have a user table in mysql, I insert data like this:

    /* prepare query */
    $query = 'INSERT INTO `users`(`first_name`, 
                                `last_name`, 
                                `gender`, 
                                `username`, 
                                `profile_picture`, 
                                `provider`, 
                                `provider_id`, 
                                `provider_username`,
                                `provider_profile`, 
                                `provider_profile_picture`,
                                `last_login`, 
                                `created_date`, 
                                `ip_address`) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), INET_ATON(?))'; 

/* Prepare an insert statement */
$stmt = $mysqli->prepare($query);

if($stmt){

 $stmt->bind_param("sssssssssss", $user['first_name'], 
                                     $user['last_name'], 
                                     $user['gender'], 
                                     $user['username'], 
                                     $user['profile_picture'], 
                                     $user['provider'],
                                     $user['id'], 
                                     $user['username'], 
                                     $user['link'],
                                     $user['profile_picture'],                                     
                                     $_SERVER['REMOTE_ADDR']);

  $stmt->execute();
 /* Execute the statement */  

I would like to make the username be equal to 'user' + userId which is autoincremental primary key field.

so that the usernames get in order:

user1
user2
user3 and so forth

what is a slick way to accomplish that?

Answer

If user_id is an AUTO_INCREMENT primary key, then you can’t do this with a single statement, even if you use a trigger.

The problem is that the AUTO_INCREMENT value isn’t generated until after the BEFORE INSERT trigger runs, but you can’t change username in the AFTER INSERT trigger.

So you just have to do the INSERT, then immediately do an UPDATE.

If user_id is not an AUTO_INCREMENT, but instead is something you specify yourself, then it’s easy, you just do the concatenation in your PHP code before you pass the values as parameters.

---

Update: You can’t do it with MySQL 5.7 generated columns either. It results in this error when you try to create the table:

Generated column ‘username’ cannot refer to auto-increment column.