CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
//SELECT @last := LAST_INSERT_ID();该变量只是等于最后ID,如何使其变为指定的ID值?
关系表是用程序控制好,还是在SQL里设置好?
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
//SELECT @last := LAST_INSERT_ID();该变量只是等于最后ID,如何使其变为指定的ID值?
关系表是用程序控制好,还是在SQL里设置好?
mysql> select * from person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
2 rows in set (0.05 sec)mysql> select * from shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
7 rows in set (0.03 sec)mysql>
//SELECT @last := LAST_INSERT_ID();INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);这条语句只是用来找出最后insrrt 的ID值。我想在插入的时候@last等于1
// SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
假设我在person表里insert 10条语句之后,再到shirt表里如何插入指定ID 5。
例如我插入ID 5 or id 6是像下面这样吗?
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', 5),
(NULL, 'polo', 'red', 5),
(NULL, 'dress', 'blue', 5),
(NULL, 't-shirt', 'white', 5);
但是这样不符合逻辑,这样要自己记住ID才行。另:小弟刚开始搞MYSQL,对问题描述的不是很给力。请多多关照新人。
-> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> name CHAR(60) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> CREATE TABLE shirt (
-> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
-> color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
-> owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> INSERT INTO person VALUES (NULL, 'Antonio Paz');
Query OK, 1 row affected (0.03 sec)mysql>
mysql> SELECT @last := LAST_INSERT_ID();
+---------------------------+
| @last := LAST_INSERT_ID() |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.02 sec)mysql>
mysql> INSERT INTO shirt VALUES
-> (NULL, 'polo', 'blue', @last),
-> (NULL, 'dress', 'white', @last),
-> (NULL, 't-shirt', 'blue', @last);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql>
mysql> INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
Query OK, 1 row affected (0.03 sec)mysql>
mysql> // SELECT @last := LAST_INSERT_ID();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '// S
LECT @last := LAST_INSERT_ID()' at line 1
mysql>
mysql> INSERT INTO shirt VALUES
-> (NULL, 'dress', 'orange', @last),
-> (NULL, 'polo', 'red', @last),
-> (NULL, 'dress', 'blue', @last),
-> (NULL, 't-shirt', 'white', @last);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
2 rows in set (0.00 sec)mysql> select * from shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 1 |
| 5 | polo | red | 1 |
| 6 | dress | blue | 1 |
| 7 | t-shirt | white | 1 |
+----+---------+--------+-------+
7 rows in set (0.00 sec)mysql>
那就插入5条后SELECT @last := LAST_INSERT_ID();