Concatenating a string and primary key Id while inserting

Question

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 1

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.

Answer 2

Assuming the username is always 'user' + userid, the slickest way I can think of to do this is to have a table with everything except username in it, and a view on top of that table that adds username. You would then do any inserts and updates on the table, and any selects that require username could be done on the view.

CREATE VIEW userview AS 
SELECT user_id, first_name, last_name, gender, profile_picture, provider, 
       provider_id, provider_username, provider_profile, provider_profile_picture,
       last_login, created_date, ip_address, 'user' + user_id as username
FROM USER