4.2. 表管理(Table)

4.2. 表管理(Table)
上一頁	第 4 章 DDL - Data Definition Language	下一頁

4.2.1. 數據類型

4.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';

4.2.2. create table ... select

創建空表

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

創建有數據的表

			
create table admin_user_history select * from admin_user;

4.2.3. modifiy table

modifiy table

			
ALTER TABLE ecs_users add user_picture varchar(255);

4.2.4. 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

4.2.5. Collate

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

4.2.6. CHARACTER

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

4.2.7. DEFAULT

4.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;

4.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;

4.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) */;

4.2.8. KEY

4.2.8.1. PRIMARY KEY

一般主鍵定義

				
PRIMARY KEY (`id`),

復合主鍵

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

4.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

4.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 */

4.2.11. Engine 存儲引擎

4.2.11.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)

4.2.11.2. 切換引擎

修改與切換引擎

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

4.2.11.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引擎表的結構,不會包含數據。

4.2.11.4. BLACKHOLE

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

4.2.11.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)

4.2.11.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

上一頁	上一級	下一頁
第 4 章 DDL - Data Definition Language	起始頁	4.3. Partitioning