Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

23.2. 表管理(Table)

23.2.1. 數據類型

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

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

23.2.3. modifiy table

modifiy table

ALTER TABLE ecs_users add user_picture varchar(255);
			

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

23.2.5. Collate

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

23.2.6. CHARACTER

			
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;
			
			

23.2.7. DEFAULT

更新時間

`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;
			
			

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

23.2.7.2. 表存儲位置(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) */;				
				
				

23.2.8. KEY

23.2.8.1. PRIMARY KEY

一般主鍵定義

PRIMARY KEY (`id`),
				

復合主鍵

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

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

23.2.10. Engine

	
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)
	
	

修改與切換引擎

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

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

23.2.10.2. BLACKHOLE

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