创建表和插入数据的语句如下,请问为什么id字段不自动增长?怎样写才能让id自动增长?请高手指教!
mysql> create table user(
-> id int(4) primary key auto_increment,
-> username varchar(20),
-> password varchar(20)
-> );
Query OK, 0 rows affected (0.13 sec)mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)mysql> insert into user values('','admin','123456');
ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1mysql> insert into user values(,'admin','123456');
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 ''admi
n','123456')' at line 1mysql> insert into user values('admin','123456');
ERROR 1136 (21S01): Column count doesn't match value count at row 1mysql> insert into user values('2','admin','123456');
Query OK, 1 row affected (0.02 sec)mysql> insert into user values('5','test','55555');
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 2 | admin | 123456 |
| 5 | test | 55555 |
+----+----------+----------+
2 rows in set (0.00 sec)mysql>
mysql> create table user(
-> id int(4) primary key auto_increment,
-> username varchar(20),
-> password varchar(20)
-> );
Query OK, 0 rows affected (0.13 sec)mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)mysql> insert into user values('','admin','123456');
ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1mysql> insert into user values(,'admin','123456');
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 ''admi
n','123456')' at line 1mysql> insert into user values('admin','123456');
ERROR 1136 (21S01): Column count doesn't match value count at row 1mysql> insert into user values('2','admin','123456');
Query OK, 1 row affected (0.02 sec)mysql> insert into user values('5','test','55555');
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 2 | admin | 123456 |
| 5 | test | 55555 |
+----+----------+----------+
2 rows in set (0.00 sec)mysql>
mysql> insert into user values('1','student','777');
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | student | 777 |
| 2 | admin | 123456 |
| 5 | test | 55555 |
+----+----------+----------+
3 rows in set (0.00 sec)mysql>
ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1/* 语法本身就是错误的 */mysql> insert into user values(,'admin','123456');
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 ''admi
n','123456')' at line 1/* 列不对应 */
mysql> insert into user values('admin','123456');
ERROR 1136 (21S01): Column count doesn't match value count at row 1/* mysql的自增列可以显示插入值,插入后自增会从最大值开始 */mysql> insert into user values('2','admin','123456');
Query OK, 1 row affected (0.02 sec)mysql> insert into user values('5','test','55555');
Query OK, 1 row affected (0.02 sec)
郁闷死了,怎么插也不对。
现在对了。