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?

There is not a slick way. To actually set the value, you would need a trigger. — Gordon Linoff, Apr 28 '14 at 01:29
. . I was sort of hoping you would think "Oh, a trigger, that's too complicated". The place to start is with the documentation: http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html. — Gordon Linoff, Apr 28 '14 at 01:36
it does sound complex for something I thought would be simple, but @bill is saying trigger wont work with auto-increment. hmm should I better drop this idea? — , Apr 28 '14 at 01:41

Bill Karwin · Accepted Answer · 2021-11-07T06:12:00.707

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.

score 0 · Answer 2 · answered May 02 '14 at 09:16

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

Concatenating a string and primary key Id while inserting

2 Answers2

Linked

Related