将A表的复制一份并重命名为B表
sql语句:
create table b as select * from aa表有一个w_id自增字段,到了b表里,w_id字段的自增属性消失了,必须要将表里的内容清空,才能设置成自增字段,这是何故?
sql语句:
create table b as select * from aa表有一个w_id自增字段,到了b表里,w_id字段的自增属性消失了,必须要将表里的内容清空,才能设置成自增字段,这是何故?
参考一下下面的测试。mysql> select * from t_wzwen;
+------+------+------+
| w_id | c1 | c2 |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
+------+------+------+
4 rows in set (0.00 sec)mysql> create table b as select * from t_wzwen;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> show create table b;
+-------+------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------
| b | CREATE TABLE `b` (
`w_id` int(11) NOT NULL DEFAULT '0',
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------
1 row in set (0.00 sec)mysql> select * from b;
+------+------+------+
| w_id | c1 | c2 |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
+------+------+------+
4 rows in set (0.00 sec)mysql> alter table b modify w_id int auto_increment primary key;
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> show create table b;
+-------+------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------
| b | CREATE TABLE `b` (
`w_id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`w_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------
1 row in set (0.00 sec)mysql>
然后用那个sql语句复制后,结果发现w_id不是自增的,直接在phpmyadmin里修改成自增的还不行,清空表记录后再修改就成功了...
create table b as select * from a这就是执行的sql语句啊,我是在phpmyadmin里执行的还要什么代码?