- 积分
- 131
- UID
- 249
- 阅读权限
- 20
- 注册时间
- 2008-1-12
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 2
|
我这有不同的情况,Look
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
| 1 | abc | NULL |
| 2 | zxc | 2008-04-25 00:00:00 |
| 3 | gh | 0000-00-00 00:00:00 |
| 4 | gh | 0000-00-00 00:00:00 |
| 5 | gh | 1970-06-05 00:00:00 |
| 6 | gh | 1969-06-05 00:00:00 |
| 7 | gh | 1900-06-05 00:00:00 |
| 8 | gh | 1800-06-05 00:00:00 |
| 9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | gh | 1000-06-05 00:00:00 |
| 12 | gh | 2007-06-05 00:00:00 |
| 13 | gh | 2007-06-05 19:15:12 |
| 14 | gh | 0000-00-00 00:00:00 |
| 15 | gh | 0000-00-00 00:00:00 |
| 16 | gh | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.01 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c2='yinm' where c1>10;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> alter table t add index (c3);
Query OK, 16 rows affected (0.04 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
| 1 | abc | NULL |
| 2 | zxc | 2008-04-25 00:00:00 |
| 3 | gh | 0000-00-00 00:00:00 |
| 4 | gh | 0000-00-00 00:00:00 |
| 5 | gh | 1970-06-05 00:00:00 |
| 6 | gh | 1969-06-05 00:00:00 |
| 7 | gh | 1900-06-05 00:00:00 |
| 8 | gh | 1800-06-05 00:00:00 |
| 9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | yinm | 1000-06-05 00:00:00 |
| 12 | yinm | 2007-06-05 00:00:00 |
| 13 | yinm | 2007-06-05 19:15:12 |
| 14 | yinm | 0000-00-00 00:00:00 |
| 15 | yinm | 0000-00-00 00:00:00 |
| 16 | yinm | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
| 1 | abc | NULL |
| 2 | zxc | 2008-04-25 00:00:00 |
| 3 | gh | 0000-00-00 00:00:00 |
| 4 | gh | 0000-00-00 00:00:00 |
| 5 | gh | 1970-06-05 00:00:00 |
| 6 | gh | 1969-06-05 00:00:00 |
| 7 | gh | 1900-06-05 00:00:00 |
| 8 | gh | 1800-06-05 00:00:00 |
| 9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | yinm | 1000-06-05 00:00:00 |
| 12 | yinm | 2007-06-05 00:00:00 |
| 13 | yinm | 2007-06-05 19:15:12 |
| 14 | yinm | 0000-00-00 00:00:00 |
| 15 | yinm | 0000-00-00 00:00:00 |
| 16 | yinm | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.00 sec)
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t | 0 | PRIMARY | 1 | c1 | A | 2 | NULL | NULL | | BTREE | |
| t | 1 | c3 | 1 | c3 | A | 2 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
在执行alter talbe时,MySQL自动提交了事务。 |
|