环境
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 | |
| 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)
现在,使用 ALTER TABLE tbl_name DROP [COLUMN] col_name
语法,删除 email
列:
mysql> ALTER TABLE user DROP COLUMN email;
Query OK, 0 rows affected (0.01 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 | |
| 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)