选择并插入mysql中的表

Mysql table

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

Mysql insert query

Mysql插入查询

insert into table1 (username)
       select id from (select id from table1) as a where 
         a.id=last_insert_id();

I am trying to insert into a table by selecting the last id from the same table and the same row,the above queries give the explanation of what i want to do.The insert query gives null value in both the id and username. The expected results is below.

我试图通过从同一个表和同一行中选择最后一个id来插入表中,上面的查询给出了我想要做的解释。插入查询在id和username中都给出了null值。预期结果如下。

id        username
001         001
002         002
003         003

2 个解决方案

#1

A possible approach

一种可能的方法

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

Here is SQLFiddle demo

这是SQLFiddle演示

A drawback of this approach is that under heavy load different concurrent users may get the same MAX(id) and you'll end up with rows that have different ids but the same username.

这种方法的一个缺点是,在负载较重的情况下,不同的并发用户可能会获得相同的MAX(id),并且最终会得到具有不同ID但具有相同用户名的行。

---

Now, the more precise way to do it involves a separate sequencing table and a BEFORE INSERT triger

现在,更精确的方法是使用单独的排序表和BEFORE INSERT triger

Proposed changed table schema

建议更改表架构

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE table1
(
   id INT(3) ZEROFILL PRIMARY KEY DEFAULT 0,
   username VARCHAR(10)
);

The trigger

DELIMITER $$
CREATE TRIGGER tg_table1_before_insert
BEFORE INSERT ON table1
FOR EACH ROW 
BEGIN
  INSERT INTO table1_seq VALUES(NULL);
  SET NEW.id = LAST_INSERT_ID(), NEW.username = LPAD(NEW.id, 3, '0');
END$$
DELIMITER ;

Now you just insert new rows into table1 like this

现在,您只需将新行插入到table1中

INSERT INTO table1 (username) 
VALUES (NULL), (NULL)

Outcome:

| ID | USERNAME |
-----------------
|  1 |      001 |
|  2 |      002 |

Here is SQLFiddle demo

这是SQLFiddle演示

#2

Why store the value at all?

为什么要存储价值?

CREATE TABLE table1 (
   id int(3) zerofill auto_increment PRIMARY KEY
);

CREATE VIEW oh_look_username
  AS
SELECT id
     , LPad(Cast(id As varchar(10)), 3, '0') As username
FROM   table1