SQL 標(biāo)準(zhǔn)使用 CREATE TABLE 語(yǔ)句創(chuàng)建數(shù)據(jù)表；MySQL 則實(shí)現(xiàn)了三種創(chuàng)建表的方法，支持自定義表結(jié)構(gòu)或者通過(guò)復(fù)制已有的表結(jié)構(gòu)來(lái)創(chuàng)建新表，本文給大家分別介紹一下這些方法的使用和注意事項(xiàng)。

CREATE TABLE

CREATE TABLE 語(yǔ)句的基本語(yǔ)法如下：

CREATE TABLE [IF NOT EXISTS] table_name
(
  column1 data_type column_constraint,
  column2 data_type,
  ...,
  table_constraint
);

使用該語(yǔ)句時(shí)，我們需要手動(dòng)定義表的結(jié)構(gòu)。以上包含的內(nèi)容如下：

• IF NOT EXISTS 表示當(dāng)該表不存在時(shí)創(chuàng)建表，當(dāng)表已經(jīng)存在時(shí)不執(zhí)行該語(yǔ)句。
• table_name 指定了表的名稱(chēng)。
• 括號(hào)內(nèi)是字段的定義；columnN 是字段的名稱(chēng)，data_type 是它的數(shù)據(jù)類(lèi)型；column_constraint 是可選的字段約束；多個(gè)字段使用逗號(hào)進(jìn)行分隔。
• table_constraint 是可選的表級(jí)約束。

其中，常見(jiàn)的約束包括主鍵、外鍵、唯一、非空、檢查約束以及默認(rèn)值。

舉例來(lái)說(shuō)，以下語(yǔ)句用于創(chuàng)建一個(gè)新表 department：

CREATE TABLE department
    ( dept_id    INTEGER NOT NULL PRIMARY KEY
    , dept_name  VARCHAR(50) NOT NULL
    ) ;

部門(mén)表 department 包含兩個(gè)字段，部門(mén)編號(hào)（dept_id）是一個(gè)整數(shù)類(lèi)型（INTEGER），不可以為空（NOT NULL），同時(shí)它還是這個(gè)表的主鍵（PRIMARY KEY）。

部門(mén)名稱(chēng)（dept_name）是一個(gè)可變長(zhǎng)度的字符串，最長(zhǎng) 50 個(gè)字符，不允許為空。

如果我們想要?jiǎng)?chuàng)建一個(gè)自定義名稱(chēng)的主鍵約束，可以使用表級(jí)約束：

CREATE TABLE department
    ( dept_id    INTEGER NOT NULL
    , dept_name  VARCHAR(50) NOT NULL
    , CONSTRAINT pk_department PRIMARY KEY (dept_id)
    ) ;

表級(jí)約束在所有字段之后定義，其中 pk_dept 是自定義的主鍵名稱(chēng)。

對(duì)于數(shù)字類(lèi)型的主鍵字段，我們可以通過(guò)自增長(zhǎng)列（auto increment）自動(dòng)生成一個(gè)唯一的數(shù)字。

例如：

CREATE TABLE department
    ( dept_id    INTEGER AUTO_INCREMENT PRIMARY KEY
    , dept_name  VARCHAR(50) NOT NULL
    ) ;

此時(shí)，我們?cè)诓迦霐?shù)據(jù)時(shí)不再需要為 dept_id 字段提供數(shù)據(jù)，MySQL 默認(rèn)會(huì)產(chǎn)生一個(gè)從 1 開(kāi)始，每次遞增 1 的數(shù)字序列。

然后我們?cè)賱?chuàng)建兩個(gè)表：

CREATE TABLE job
    ( job_id         INTEGER NOT NULL PRIMARY KEY
    , job_title      VARCHAR(50) NOT NULL
    ) ;
    
CREATE TABLE employee
    ( emp_id    INTEGER NOT NULL PRIMARY KEY
    , emp_name  VARCHAR(50) NOT NULL
    , sex       VARCHAR(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR(100) NOT NULL
    , CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
    , CONSTRAINT ck_emp_salary CHECK (salary > 0)
    , CONSTRAINT uk_emp_email UNIQUE (email)
    , CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    , CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
    , CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
    ) ;

job 表用于存儲(chǔ)職位信息，和部門(mén)表相似，比較簡(jiǎn)單。

employee 表用于存儲(chǔ)員工信息，包含的字段和約束如下：

• 員工編號(hào)（emp_id）是一個(gè)整數(shù)類(lèi)型（INTEGER），不可以為空（NOT NULL），同時(shí)它還是這個(gè)表的主鍵（PRIMARY KEY）。
• 員工姓名（emp_name）是一個(gè)可變長(zhǎng)度的字符串，最長(zhǎng) 50 個(gè)字符，不允許為空。
• 性別（sex）是一個(gè)可變長(zhǎng)度的字符串，最長(zhǎng) 10 個(gè)字符，不允許為空；另外，我們通過(guò)表級(jí)約束 ck_emp_sex 限制了性別的取值只能為“男”或者“女”。
• 部門(mén)編號(hào)（dept_id）代表了員工所在的部門(mén)，因此通過(guò)外鍵約束 fk_emp_dept 引用了部門(mén)表的主鍵字段。
• 經(jīng)理編號(hào)（manager）代表了員工的直接上級(jí)，可能為空。外鍵約束 fk_emp_manager 表示經(jīng)理也屬于員工。
• 入職日期（hire_date）是一個(gè) DATE 類(lèi)型的字段，不能為空。
• 職位編號(hào)（job_id）代表了員工的職位，因此通過(guò)外鍵 fk_emp_job 引用了職位表的主鍵字段。
• 月薪（salary）是一個(gè)支持兩位小數(shù)的數(shù)字，不能為空。檢查約束 ck_emp_salary 要求月薪必須大于零。
• 獎(jiǎng)金（bonus）是一個(gè)可選的數(shù)字字段。
• 電子郵箱（email）是一個(gè)可變長(zhǎng)度的字符串，最長(zhǎng)100 個(gè)字符，不允許為空。檢查約束 uk_emp_email 要求每個(gè)員工的電子郵箱都不相同。

CREATE TABLE … LIKE

除了手動(dòng)定義表的結(jié)構(gòu)之外，MySQL 還提供了復(fù)制已有表結(jié)構(gòu)的方法：

CREATE TABLE [IF NOT EXISTS] table_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

MySQL 的 LIKE 語(yǔ)法只復(fù)制表結(jié)構(gòu)，包括字段的屬性和索引，但是不復(fù)制數(shù)據(jù)。

例如：

CREATE TABLE emp_copy
  LIKE employee;

以上語(yǔ)句基于 employee 表的結(jié)構(gòu)復(fù)制生成一個(gè)新的表 emp_copy。

mysql> show create table emp_copy \G
*************************** 1. row ***************************
       Table: emp_copy
Create Table: CREATE TABLE `emp_copy` (
  `emp_id` int NOT NULL,
  `emp_name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `dept_id` int NOT NULL,
  `manager` int DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` int NOT NULL,
  `salary` decimal(8,2) NOT NULL,
  `bonus` decimal(8,2) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `uk_emp_email` (`email`),
  KEY `fk_emp_dept` (`dept_id`),
  KEY `fk_emp_job` (`job_id`),
  KEY `fk_emp_manager` (`manager`),
  CONSTRAINT `emp_copy_chk_1` CHECK ((`salary` > 0)),
  CONSTRAINT `emp_copy_chk_2` CHECK ((`sex` in (_utf8mb4'男',_utf8mb4'女')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

對(duì)于 CREATE TABLE … LIKE 命令，目標(biāo)表會(huì)保留原始表中的主鍵、唯一約束、非空約束、表達(dá)式默認(rèn)值、檢查約束（自動(dòng)生成約束名），同時(shí)還會(huì)保留原始表中的計(jì)算列定義。

CREATE TABLE … LIKE 命令不會(huì)保留外鍵約束（但是會(huì)保留外鍵索引），以及任何 DATA DIRECTORY 或者 INDEX DIRECTORY 表屬性選項(xiàng)。

如果原始表是一個(gè) TEMPORARY 表，CREATE TABLE … LIKE 不會(huì)保留 TEMPORARY 關(guān)鍵字。如果想要?jiǎng)?chuàng)建一個(gè)臨時(shí)表，可以使用 CREATE TEMPORARY TABLE … LIKE。

使用 mysql 表空間、InnoDB 系統(tǒng)表空間（innodb_system）或者通用表空間創(chuàng)建的表包含一個(gè) TABLESPACE 屬性，表示該表所在的表空間。目前，無(wú)論 innodb_file_per_table 設(shè)置為什么參數(shù)，CREATE TABLE … LIKE 都會(huì)保留 TABLESPACE 屬性。為了避免復(fù)制新表時(shí)使用原始表的 TABLESPACE 屬性，可以使用下面介紹的第三種方法。例如：

CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

以上語(yǔ)句會(huì)基于 orig_tbl 創(chuàng)建一個(gè)新的空表 new_tbl，具體參考下一節(jié)內(nèi)容。

CREATE TABLE … LIKE 使用原始表的所有 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值創(chuàng)建目標(biāo)表。

另外，LIKE 只能基于表進(jìn)行復(fù)制，而不能復(fù)制視圖。

CREATE TABLE … SELECT

在 MySQL 中復(fù)制表結(jié)構(gòu)的另一種方法就是利用查詢(xún)語(yǔ)句的結(jié)果定義字段和復(fù)制的數(shù)據(jù)：

CREATE TABLE table_name
  [AS] SELECT ...;

其中的 SELECT 語(yǔ)句定義了新表的結(jié)構(gòu)和數(shù)據(jù)。

以下示例基于查詢(xún)的結(jié)果創(chuàng)建了一個(gè)新表：emp_devp，表中包含了研發(fā)部的所有員工。

CREATE TABLE emp_devp
    AS
SELECT e.*
  FROM employee e
  JOIN department d
    ON (d.dept_id = e.dept_id AND d.dept_name = '研發(fā)部');

對(duì)于這種語(yǔ)法，MySQL 實(shí)際上是在已有目標(biāo)表中增加新的字段。

例如：

CREATE TABLE t1(col1 INTEGER, col2 INTEGER);
INSERT INTO t1(col1, col2) VALUES(1, 1), (2, 4);

CREATE TABLE t2(id INTEGER AUTO_INCREMENT PRIMARY KEY)
       ENGINE=InnoDB
    AS SELECT col1, col2 FROM t1;

我們首先為 t2 指定了一個(gè)自增 id，然后將 t1 的查詢(xún)結(jié)果添加到該字段的后面。其中，ENGINE 選項(xiàng)屬于 CREATE TABLE 語(yǔ)句，因此需要位于 SELECT 語(yǔ)句之前。

查詢(xún) t2 可以看到以上語(yǔ)句不僅復(fù)制了表結(jié)構(gòu)，同時(shí)還復(fù)制了 t1 中的數(shù)據(jù)：

TABLE t2;

+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    4 |
+----+------+------+
2 rows in set (0.00 sec)

如果只想復(fù)制結(jié)構(gòu)，不需要復(fù)制數(shù)據(jù)，可以在查詢(xún)語(yǔ)句中增加 LIMIT 0 或者 WHERE 1=0 條件。

如果在 SELECT 語(yǔ)句前面增加 IGNORE 或者 REPLACE 關(guān)鍵字，可以處理復(fù)制數(shù)據(jù)時(shí)導(dǎo)致唯一鍵沖突的數(shù)據(jù)行。對(duì)于 IGNORE，源數(shù)據(jù)中和目標(biāo)表重復(fù)的數(shù)據(jù)行就會(huì)被丟棄；對(duì)于 REPLACE，使用新數(shù)據(jù)行替換目標(biāo)表中的已有數(shù)據(jù)行。如果沒(méi)有指定任何選項(xiàng)，唯一鍵沖突將會(huì)返回錯(cuò)誤。

CREATE TABLE … SELECT 命令不會(huì)自動(dòng)創(chuàng)建任何索引，這樣可以使得該語(yǔ)句盡量靈活。如果想要?jiǎng)?chuàng)建索引，可以在 SELECT 語(yǔ)句之前指定。

例如：

CREATE TABLE t3(id INTEGER PRIMARY KEY)
AS SELECT col1 as id, col2 FROM t1;

CREATE TABLE … SELECT 命令不會(huì)保留計(jì)算列的定義，也不會(huì)保留默認(rèn)值定義。同時(shí)某些數(shù)據(jù)類(lèi)型可能產(chǎn)生轉(zhuǎn)換。例如，AUTO_INCREMENT 屬性不會(huì)被保留，VARCHAR 類(lèi)型被轉(zhuǎn)換為 CHAR 類(lèi)型。保留的屬性包括 NULL（NOT NULL）以及 CHARACTER SET、COLLATION、COMMENT 和 DEFAULT 子句。

使用 CREATE TABLE … SELECT 命令創(chuàng)建表時(shí)，需要為查詢(xún)語(yǔ)句中的函數(shù)和表達(dá)式指定一個(gè)別名，否則該命令可能失敗或者創(chuàng)建意料之外的字段名：

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

對(duì)于 CREATE TABLE … SELECT 命令，如果我們指定了 IF NOT EXISTS 并且目標(biāo)表已經(jīng)存在，不會(huì)將數(shù)據(jù)復(fù)制到目標(biāo)表，同時(shí)該語(yǔ)句不會(huì)寫(xiě)入日志文件。

CREATE TABLE … SELECT 命令不支持 FOR UPDATE 選項(xiàng)。

CREATE TABLE … SELECT 命令只會(huì)應(yīng)用字段的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 屬性。表和索引的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 屬性不會(huì)被應(yīng)用，除非為目標(biāo)表明確指定這些選項(xiàng)。

總結(jié)

本文通過(guò)一些案例介紹了 MySQL 中三種創(chuàng)建表的方法和注意事項(xiàng)。

僅為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論