mysql中如何改变字段或者列的顺序

 

创建了一个这样的数据表，想把id放到第一列，因为是主键，并且是自增的：

mysql> select * from student
    -> ;
+-------+-------+-------+----+
| class | name  | score | id |
+-------+-------+-------+----+
|     1 | user1 |   100 |  1 |
|     1 | user  |    45 |  2 |
|     1 | user1 |    80 |  3 |
|     1 | user  |    62 |  4 |
|     1 | user1 |   100 |  5 |
|     2 | user  |   108 |  6 |
|     2 | user1 |    90 |  7 |
|     2 | user  |    24 |  8 |
+-------+-------+-------+----+
8 rows in set (0.00 sec)

 

原来的顺序如上所示，怎么把id放在在前面，并且数据不动，属性不变呢？

废话不多说，直接上句子：

 

mysql> alter table student modify id int(10) unsigned auto_increment first;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| score | int(10)          | YES  |     | NULL    |                |
| class | int(10)          | NO   |     | NULL    |                |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from student;
+----+-------+-------+-------+
| id | score | class | name  |
+----+-------+-------+-------+
|  1 |   100 |     1 | user1 |
|  2 |    45 |     1 | user  |
|  3 |    80 |     1 | user1 |
|  4 |    62 |     1 | user  |
|  5 |   100 |     1 | user1 |
|  6 |   108 |     2 | user  |
|  7 |    90 |     2 | user1 |
|  8 |    24 |     2 | user  |
+----+-------+-------+-------+
8 rows in set (0.00 sec)

这是放到第一位，如果要把name放到id之后呢？这样写就可以了（first 换成 after即可）：

 

 

mysql> alter table student modify name varchar(10) after id;
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+-------+-------+
| id | name  | score | class |
+----+-------+-------+-------+
|  1 | user1 |   100 |     1 |
|  2 | user  |    45 |     1 |
|  3 | user1 |    80 |     1 |
|  4 | user  |    62 |     1 |
|  5 | user1 |   100 |     1 |
|  6 | user  |   108 |     2 |
|  7 | user1 |    90 |     2 |
|  8 | user  |    24 |     2 |
+----+-------+-------+-------+
8 rows in set (0.00 sec)

完美解决问题！

添加一列:

ALTER TABLE `ocs_reqs` ADD `sw_atom_ids` VARCHAR(255) NOT NULL AFTER `create_time`;