插入时串联字符串和主键ID [英] Concatenating a string and primary key Id while inserting

问题描述

我在mysql中有一个用户表，我插入这样的数据:

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 */  

我想使username等于'user' + userId，这是自动递增的主键字段.

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

以便用户名井井有条:

user1
user2
user3 and so forth

有什么精巧的方法可以做到这一点?

what is a slick way to accomplish that?

推荐答案

如果user_id是AUTO_INCREMENT主键，那么即使使用触发器，也无法使用单个语句来完成此操作.

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.

问题在于，直到BEFORE INSERT触发器运行之后才生成AUTO_INCREMENT值，但是您不能在AFTER INSERT触发器中更改username.

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.

因此，您只需要执行INSERT，然后立即执行UPDATE.

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

如果user_id不是 是AUTO_INCREMENT，而是您自己指定的东西，那么很容易，您只需在PHP代码中进行串联，然后再将值作为参数传递.

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.

这篇关于插入时串联字符串和主键ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！