目錄

• CREATE TABLE
• CREATE TABLE … LIKE
• CREATE TABLE … SELECT
• 總結

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

CREATE TABLE

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

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

使用該語句時，我們需要手動定義表的結構。以上包含的內容如下：

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

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

舉例來說，以下語句用于創(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 默認會產(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

除了手動定義表的結構之外，MySQL 還提供了復制已有表結構的方法：

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

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

例如：

CREATE TABLE emp_copy
  LIKE employee;

以上語句基于 employee 表的結構復制生成一個新的表 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 命令，目標表會保留原始表中的主鍵、唯一約束、非空約束、表達式默認值、檢查約束（自動生成約束名），同時還會保留原始表中的計算列定義。

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

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

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

CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

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

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

另外，LIKE 只能基于表進行復制，而不能復制視圖。

CREATE TABLE … SELECT

在 MySQL 中復制表結構的另一種方法就是利用查詢語句的結果定義字段和復制的數(shù)據(jù)：

CREATE TABLE table_name
  [AS] SELECT ...;

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

以下示例基于查詢的結果創(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 實際上是在已有目標表中增加新的字段。

例如：

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 的查詢結果添加到該字段的后面。其中，ENGINE 選項屬于 CREATE TABLE 語句，因此需要位于 SELECT 語句之前。

查詢 t2 可以看到以上語句不僅復制了表結構，同時還復制了 t1 中的數(shù)據(jù)：

TABLE t2;

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

如果只想復制結構，不需要復制數(shù)據(jù)，可以在查詢語句中增加 LIMIT 0 或者 WHERE 1=0 條件。

如果在 SELECT 語句前面增加 IGNORE 或者 REPLACE 關鍵字，可以處理復制數(shù)據(jù)時導致唯一鍵沖突的數(shù)據(jù)行。對于 IGNORE，源數(shù)據(jù)中和目標表重復的數(shù)據(jù)行就會被丟棄；對于 REPLACE，使用新數(shù)據(jù)行替換目標表中的已有數(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 命令不會保留計算列的定義，也不會保留默認值定義。同時某些數(shù)據(jù)類型可能產(chǎn)生轉換。例如，AUTO_INCREMENT 屬性不會被保留，VARCHAR 類型被轉換為 CHAR 類型。保留的屬性包括 NULL（NOT NULL）以及 CHARACTER SET、COLLATION、COMMENT 和 DEFAULT 子句。

使用 CREATE TABLE … SELECT 命令創(chuàng)建表時，需要為查詢語句中的函數(shù)和表達式指定一個別名，否則該命令可能失敗或者創(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 并且目標表已經(jīng)存在，不會將數(shù)據(jù)復制到目標表，同時該語句不會寫入日志文件。

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

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

總結

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

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