目錄

• CREATE TABLE
• CREATE TABLE … LIKE
• CREATE TABLE … SELECT
• 總結(jié)

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

CREATE TABLE

CREATE TABLE 語句的基本語法如下：

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

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

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

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

舉例來說，以下語句用于創(chuàng)建一個新表 department：

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

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

部門名稱（dept_name）是一個可變長度的字符串，最長 50 個字符，不允許為空。

如果我們想要創(chuàng)建一個自定義名稱的主鍵約束，可以使用表級約束：

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

表級約束在所有字段之后定義，其中 pk_dept 是自定義的主鍵名稱。

對于數(shù)字類型的主鍵字段，我們可以通過自增長列（auto increment）自動生成一個唯一的數(shù)字。

例如：

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

此時，我們在插入數(shù)據(jù)時不再需要為 dept_id 字段提供數(shù)據(jù)，MySQL 默認(rèn)會產(chǎn)生一個從 1 開始，每次遞增 1 的數(shù)字序列。

然后我們再創(chuàng)建兩個表：

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 表用于存儲職位信息，和部門表相似，比較簡單。

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

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

CREATE TABLE … LIKE

除了手動定義表的結(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 語法只復(fù)制表結(jié)構(gòu)，包括字段的屬性和索引，但是不復(fù)制數(shù)據(jù)。

例如：

CREATE TABLE emp_copy
  LIKE employee;

以上語句基于 employee 表的結(jié)構(gòu)復(fù)制生成一個新的表 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)

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

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

如果原始表是一個 TEMPORARY 表，CREATE TABLE … LIKE 不會保留 TEMPORARY 關(guān)鍵字。如果想要創(chuàng)建一個臨時表，可以使用 CREATE TEMPORARY TABLE … LIKE。

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

CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

以上語句會基于 orig_tbl 創(chuàng)建一個新的空表 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)的另一種方法就是利用查詢語句的結(jié)果定義字段和復(fù)制的數(shù)據(jù)：

CREATE TABLE table_name
  [AS] SELECT ...;

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

以下示例基于查詢的結(jié)果創(chuàng)建了一個新表：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ā)部");

對于這種語法，MySQL 實際上是在已有目標(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 指定了一個自增 id，然后將 t1 的查詢結(jié)果添加到該字段的后面。其中，ENGINE 選項屬于 CREATE TABLE 語句，因此需要位于 SELECT 語句之前。

查詢 t2 可以看到以上語句不僅復(fù)制了表結(jié)構(gòu)，同時還復(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ù)，可以在查詢語句中增加 LIMIT 0 或者 WHERE 1=0 條件。

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

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

例如：

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

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

使用 CREATE TABLE … SELECT 命令創(chuàng)建表時，需要為查詢語句中的函數(shù)和表達(dá)式指定一個別名，否則該命令可能失敗或者創(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;

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

CREATE TABLE … SELECT 命令不支持 FOR UPDATE 選項。

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

總結(jié)

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

僅為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持。