MySQL - 修改表：新增一列环境修改表：新增一列参考

文章目录

• 环境
• 修改表：新增一列
• 参考

环境

MySQL 数据库版本信息：

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Server version:         5.7.30 MySQL Community Server (GPL)
           

修改表：新增一列

原来的表结构：

mysql> describe user;
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                   | Type         | Null | Key | Default           | Extra                       |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| id                      | bigint(20)   | NO   | PRI | NULL              | auto_increment              |
| username                | varchar(256) | NO   |     | NULL              |                             |
| password                | varchar(256) | NO   |     | NULL              |                             |
| account_non_expired     | tinyint(1)   | YES  |     | 1                 |                             |
| account_non_locked      | tinyint(1)   | YES  |     | 1                 |                             |
| credentials_non_expired | tinyint(1)   | YES  |     | 1                 |                             |
| enabled                 | tinyint(1)   | YES  |     | 1                 |                             |
| deleted                 | tinyint(1)   | YES  |     | 0                 |                             |
| create_time             | timestamp    | YES  |     | CURRENT_TIMESTAMP |                             |
| update_time             | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
10 rows in set (0.00 sec)
           

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `password` varchar(256) NOT NULL,
  `account_non_expired` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user''s account is valid (ie non-expired), false (0) if no longer valid (ie expired).',
  `account_non_locked` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user is not locked, false (0) otherwise.',
  `credentials_non_expired` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user''s credentials are valid (ie non-expired), false (0) if no longer valid (ie expired).',
  `enabled` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user is enabled, false (0) otherwise.',
  `deleted` tinyint(1) DEFAULT '0' COMMENT 'true (1) if this record is deleted, false (0, default) otherwise.',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
           

现在，使用

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

语法，在

username

之后添加一列

email

：

mysql> ALTER TABLE user ADD COLUMN email varchar(256) DEFAULT NULL AFTER username;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

添加成功，新的表结构：

mysql> describe user;
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                   | Type         | Null | Key | Default           | Extra                       |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| id                      | bigint(20)   | NO   | PRI | NULL              | auto_increment              |
| username                | varchar(256) | NO   |     | NULL              |                             |
| email                   | varchar(256) | YES  |     | NULL              |                             |
| password                | varchar(256) | NO   |     | NULL              |                             |
| account_non_expired     | tinyint(1)   | YES  |     | 1                 |                             |
| account_non_locked      | tinyint(1)   | YES  |     | 1                 |                             |
| credentials_non_expired | tinyint(1)   | YES  |     | 1                 |                             |
| enabled                 | tinyint(1)   | YES  |     | 1                 |                             |
| deleted                 | tinyint(1)   | YES  |     | 0                 |                             |
| create_time             | timestamp    | YES  |     | CURRENT_TIMESTAMP |                             |
| update_time             | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.00 sec)
           

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(256) DEFAULT NULL,
  `password` varchar(256) NOT NULL,
  `account_non_expired` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user''s account is valid (ie non-expired), false (0) if no longer valid (ie expired).',
  `account_non_locked` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user is not locked, false (0) otherwise.',
  `credentials_non_expired` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user''s credentials are valid (ie non-expired), false (0) if no longer valid (ie expired).',
  `enabled` tinyint(1) DEFAULT '1' COMMENT 'true (1, default) if the user is enabled, false (0) otherwise.',
  `deleted` tinyint(1) DEFAULT '0' COMMENT 'true (1) if this record is deleted, false (0, default) otherwise.',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
           

参考

MySQL 5.7 Reference Manual / … / ALTER TABLE Statement