SQL INSERT语句的时候,要避免 指定那个自增的字段.否则会发生主键的冲突。
通过 ALTER TABLE语句 可以修改 自增的数值, 但是只能增加,不能减少。
TRUNCATE TABLE 语句,会将自增ID重置为零。
mysql> CREATE TABLE test_create_tab2 (
-> id INT AUTO_INCREMENT,
-> val VARCHAR(10),
-> PRIMARY KEY (id)
-> )
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO test_create_tab2(val) VALUES ('NO id')
Query OK, 1 row affected (0.03 sec)
mysql> select last_insert_id() as id
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> INSERT INTO test_create_tab2(val) VALUES ('NO id 2')
Query OK, 1 row affected (0.03 sec)
mysql> select last_insert_id() as id
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
mysql> select * from test_create_tab2
+----+---------+
| id | val |
+----+---------+
| 1 | NO id |
| 2 | NO id 2 |
+----+---------+
2 rows in set (0.00 sec)
直接查询出来结果我没想出来如何做我给你看下如何分别查type=1和type=2的吧
set @i := 0
select concat(@i := @i + 1,'.',title) as `title`,type
from (SELECT 'A' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'B' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'C' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'D' title, 2 TYPE FROM DUAL UNION ALL
SELECT 'E' title, 2 TYPE FROM DUAL) a
where type=1
order by title asc
set @i := 0
select concat(@i := @i + 1,'.',title) as `title`,type
from (SELECT 'A' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'B' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'C' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'D' title, 2 TYPE FROM DUAL UNION ALL
SELECT 'E' title, 2 TYPE FROM DUAL) a
where type=2
order by title asc
这样将两个结果集放在一起就是你那个样子了
但是我水平有限。。。不知道这种带参数设定的结果集怎么弄一起
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)