Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

60.2. 表管理(Table)

60.2.1. 数据类型

60.2.1.1. SET 集合类型

SET 集合类型,此类型适合用于多项选择场景,例如保存表单中的checkbox。

				
CREATE TABLE `QA` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`question` VARCHAR(255) NOT NULL COMMENT '问题描述',
	`answer` SET('A','B','C','D') NOT NULL COMMENT '问题答案',
	PRIMARY KEY (`id`)
)
COMMENT='Multiple Choice'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
				
				

插入数据

				
INSERT INTO `QA` (`id`, `question`, `answer`) VALUES
	(1, 'Netkiller 系列手札始于那一年? A.2000年,B.2008年,C.2010年,D.2016年', 'A'),
	(2, 'Netkiller 系列手札有哪些? A.《Netkiller Scals 手札》, B.《Netkiller Java 手札》, C.《Netkiller Linux 手札》, D.《Netkiller EMC 手札》', 'B,C'),
	(3, 'XXXXXXXXX', 'C,D'),
	(4, 'XXXXXXXXX', 'A,B,C'),
	...
	...
	(1000, 'XXXXXXXXXX', 'B,C,D'),
	...
	...
	(5000, 'XXXXXXXXXX', 'A,B,C,D');
				
				

查询 SET 结果集,MySQL为SET配备了FIND_IN_SET函数

				
select * from QA where FIND_IN_SET('B',`answer`);
				
				

下面两种方法也能实现,但不推荐使用。

				
select question, answer from QA where locate('B',answer)>0;
select question, answer from QA where POSITION('B' in answer)>0;				
				
				

查询多个答案

				
select question, answer from QA where answer = 'B,C';				
				
				

60.2.2. 基于现有表结构创建新空表

			
CREATE TABLE `test`.`old_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));
  
CREATE TABLE new_table LIKE old_table;			
			
			

			
mysql> show create table `test`.new_table;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                       |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| new_table | CREATE TABLE `new_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)			
			
			

60.2.3. 基于已存在表创建新表

create table ... select

创建空表

			
create table admin_user_history select * from admin_user where 1 <> 1;
			
			

创建有数据的表

			
create table admin_user_history select * from admin_user;
			
			

60.2.4. 修改表

modifiy table

			
ALTER TABLE ecs_users add user_picture varchar(255);
			
			

60.2.5. 临时表

TEMPORARY Table

临时表将在你连接期间存在。一旦断开时将自动删除表并释放所用的空间。你在连接期间删除该表也同样释放空间。

			
CREATE TEMPORARY TABLE tmp_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
)
			
			

声明临时表是一个HEAP表,允许你指定在内存中创建它

			
CREATE TEMPORARY TABLE tmp_mem_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
) TYPE = HEAP
			
			

60.2.6. CHARACTER

			
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;
alter table <table_name> convert to charset utf8mb4;
			
			

修改表字符集

			
ALTER TABLE `tmp_cats`  COLLATE='utf8_general_ci',  CONVERT TO CHARSET utf8;
			
			

60.2.7. DEFAULT

60.2.7.1. AUTO_INCREMENT

定义 AUTO_INCREMENT 起始值

				
CREATE TABLE `bank_account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增唯一ID',
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '帐号名称(Name on account)',
	PRIMARY KEY (`id`)
)
COMMENT='银行帐号'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;				
				
				

设置 AUTO_INCREMENT

				
ALTER TABLE `accounts`
	AUTO_INCREMENT=792257;
				
				

60.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE

				
alter table cms.article ADD  COLUMN `mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间';				
				
				

更新时间

`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',

				
CREATE TABLE `bank_account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增唯一ID',
	`bank_name` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '银行名字(Bank Name)',
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '帐号名称(Name on account)',
	`account_number` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '银行帐号(Account Number)',
	`branch_location` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '支行位置(Branch Location)',
	`description` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '银行描述',
	`status` ENUM('Y','N') NOT NULL DEFAULT 'N' COMMENT '银行帐号状态',
	`ctime` DATETIME NOT NULL COMMENT '创建时间',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',
	PRIMARY KEY (`id`)
)
COMMENT='银行帐号'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;
				
				

60.2.7.3. 表存储位置(DATA DIRECTORY)

				
CREATE TABLE IF NOT EXISTS `tab_name` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL,
  KEY `Index 1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (YEAR(purchased))
(PARTITION p0 VALUES IN (1990) DATA DIRECTORY = '/www/data' ENGINE = InnoDB) */;				
				
				

60.2.8. KEY

60.2.8.1. PRIMARY KEY

一般主键定义

				
PRIMARY KEY (`id`),
				
				

复合主键

				
PRIMARY KEY (`id`, `user_id`),
				
				

60.2.9. AUTO_INCREMENT 定义初始值

			
DROP TABLE IF EXISTS users;
CREATE TABLE user(
    id   	INT   NOT NULL AUTO_INCREMENT
PRIMARY KEY(id)
)ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;			
			
			

修改 auto_incremenrt 起始值

			
alter table tab1 auto_incremenrt=n			
			
			

60.2.10. COMMENT

			
ALTER TABLE `neo`.`stuff` COMMENT = '用户表' ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `name` `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '姓名'  ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `password` `password` VARCHAR(50) NULL DEFAULT NULL COMMENT '用户密码' ;


CREATE TABLE `stuff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT ''姓名'',
  `password` varchar(50) DEFAULT NULL COMMENT ''用户密码'',
  `created` date NOT NULL DEFAULT ''0000-00-00'',
  PRIMARY KEY (`id`,`created`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT=''用户表''
/*!50100 PARTITION BY HASH (year(created))
PARTITIONS 10 */
			
			

60.2.11. 修改表名

			
ALTER TABLE old_table_name  RENAME TO new_table_name; 
RENAME old_table _name TO new_table_name;			
			
			

60.2.12. Engine 存储引擎

60.2.12.1. 显示当前数据库支持引擎

		
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
		
		

60.2.12.2. 切换引擎

修改与切换引擎

		
ALTER TABLE `test` ENGINE=BLACKHOLE;
ALTER TABLE `test` ENGINE=InnoDB;
		
		

60.2.12.3. FEDERATED

启用 FEDERATED 引擎, 服务器环境 Ubuntu 13.04

$ sudo vim /etc/mysql/conf.d/federated.cnf
[mysqld]
federated

$ sudo service mysql restart
		
		
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
		
		

A 服务器

CREATE TABLE `t1` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`sex` ENUM('Y','N') NULL DEFAULT 'Y',
	`passwd` VARCHAR(50) NULL DEFAULT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4;
		

B 服务器

DROP TABLE `users`;

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`sex` ENUM('Y','N') NULL DEFAULT 'Y',
	`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';
		

上面字段描述是你需要的字段,并非所有字段。这里屏蔽了passwd字段

[提示]提示

connection = 'mysql://用户名:密码@主机:端口/数据库/表名'

		
mysql> DROP TABLE `users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `users` (
    -> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR(50) NOT NULL,
    -> `sex` ENUM('Y','N') NULL DEFAULT 'Y',
    -> `ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> desc users;
+-------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type             | Null | Key | Default             | Extra                       |
+-------+------------------+------+-----+---------------------+-----------------------------+
| id    | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name  | varchar(50)      | NO   |     | NULL                |                             |
| sex   | enum('Y','N')    | YES  |     | Y                   |                             |
| ctime | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
| mtime | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-------+------------------+------+-----+---------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> select * from users;
+----+------+------+---------------------+---------------------+
| id | name | sex  | ctime               | mtime               |
+----+------+------+---------------------+---------------------+
|  1 | neo  | Y    | 0000-00-00 00:00:00 | 2013-05-17 18:05:09 |
|  2 | zen  | Y    | 0000-00-00 00:00:00 | 2013-05-17 18:05:11 |
|  3 | lily | N    | 0000-00-00 00:00:00 | 2013-05-17 18:05:22 |
+----+------+------+---------------------+---------------------+
3 rows in set (0.01 sec)
		
		
[注意]FEDERATED 与 mysqldump 问题!

切记,mysqldump 只会dump出使用FEDERATED引擎表的结构,不会包含数据。

60.2.12.4. BLACKHOLE

CREATE TABLE test(id INT, val CHAR(10)) ENGINE = BLACKHOLE;
		

60.2.12.5. ARCHIVE

归档(是适用于存放大量数据的存储引擎), 仅支持select、insert操作; 不支持delete 、update、索引等操作;使用zlib无损算法压缩数据,节省磁盘空间;

适用场景:适用于大量可查询但不能删除的历史数据保存;

基于 order 表创建 order_audit 归档表

		
create table order_audit engine=archive as select * from `order`;
		
		

order_audit 表结构如下

		
CREATE TABLE `order_audit` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单ID',
  `name` varchar(45) NOT NULL COMMENT '订单名称',
  `price` float NOT NULL COMMENT '价格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8		
		
		
		
mysql> show table status like 'order_audit';
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
| order_audit | ARCHIVE |      10 | Compressed |    4 |           2215 |        8861 |               0 |            0 |         0 |           NULL | NULL        | 2017-11-16 17:30:34 | NULL       | utf8_general_ci |     NULL |                |         |
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)		
		
		

60.2.12.6. CSV

创建表

		
CREATE TABLE `csv_table` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8		
		
		

查看表状态

		
mysql> show table status like 'csv_table';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| csv_table | CSV    |      10 | Dynamic    |    2 |              0 |           0 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)		
		
		

插入数据

		
insert into csv_table values (1,'Neo',37),(2,'Jam',40);		
		
		

查看数据

		
mysql> SELECT * FROM test.csv_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Neo  |  37 |
|  2 | Jam  |  40 |
+----+------+-----+
2 rows in set (0.00 sec)
		
		

CSV 引擎是可以直接将csv文件复制出来的,表存储在 /var/lib/mysql/ 目录

		
root@netkiller /etc/nginx/conf.d % ls -1 /var/lib/mysql/test/csv*
/var/lib/mysql/test/csv_table.CSM
/var/lib/mysql/test/csv_table.CSV
/var/lib/mysql/test/csv_table.frm		
		
		

.*CSM, *.frm 是表结构文件,*.CSV 是我们需要的文件,纯文本,可以使用Excel打开。

		
root@netkiller /etc/nginx/conf.d % cat /var/lib/mysql/test/csv_table.CSV
1,"Neo",37
2,"Jam",40