插入时连接字符串和主键 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.

更新:您也无法使用 MySQL 5.7 生成的列来执行此操作.当您尝试创建表时会导致此错误:

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.

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