通过从两列中选择值到同一个表中的列来插入

Please I am inserting values by selecting two values from two columns into one column in the same table.Below are my queries.

请通过从两列中选择两个值到同一个表中的一列来插入值。下面是我的查询。

create table table1(
 id int(3) zerofill auto_increment primary key,
 prefix varchar(10) default "AB",
 username varchar(10)
)
engine=innodb;

Mysql insert query

Mysql插入查询

 insert into table1 (username) 
 select prefix + (LPAD(Coalesce(MAX(id),0) + 1,3, '0'))
  from table1;

The above insert query does not work,it gives null in the username column,please any help is appreciated.Thanks. The expected results is below.

上面的插入查询不起作用,它在用户名栏中给出null,请任何帮助表示赞赏。谢谢。预期结果如下。

   id    Prefix   username
   001    AB       AB001
   002    AB       AB002
   003    AB       AB003

2 个解决方案

#1

Problems:

1. As Matt mentioned you need to use CONCAT() in MySql

正如Matt所说,你需要在MySql中使用CONCAT()

2. For the first record being inserted your SELECT returns NULL therefore you need to use COALESCE() or IFNULL() to get DEFAULT value for concatenation

对于插入的第一个记录,SELECT返回NULL,因此您需要使用COALESCE()或IFNULL()来获取连接的DEFAULT值

Your query should look like this

您的查询应如下所示

INSERT INTO table1 (username)
SELECT CONCAT(COALESCE(prefix, 'AB'), LPAD(COALESCE(MAX(id), 0) + 1, 3, '0'))
  FROM table1

Outcome:

| ID | PREFIX | USERNAME |
--------------------------
|  1 |     AB |    AB001 |
|  2 |     AB |    AB002 |

Here is SQLFddle

这是SQLFddle

#2

+ is not the string concatenation operator in MySQL. If you're using sql_mode=PIPES_AS_CONCAT (or equivalent), then:

+不是MySQL中的字符串连接运算符。如果您使用的是sql_mode = PIPES_AS_CONCAT(或等效的),那么:

insert into table1 (username) 
select prefix || (LPAD(Coalesce(MAX(id),0) + 1,3, '0'))
from table1;

otherwise use CONCAT.

否则使用CONCAT。