插入时串联字符串和主键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屋!