MySQL中建立表的三種方法彙總

2023-02-22 06:01:01

CREATE TABLE

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

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

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

IF NOT EXISTS 表示當該表不存在時建立表，當表已經存在時不執行該語句。
table_name 指定了表的名稱。
括號內是欄位的定義；columnN 是欄位的名稱，data_type 是它的資料型別；column_constraint 是可選的欄位約束；多個欄位使用逗號進行分隔。
table_constraint 是可選的表級約束。

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

舉例來說，以下語句用於建立一個新表 department：

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

部門表 department 包含兩個欄位，部門編號（dept_id）是一個整數型別（INTEGER），不可以為空（NOT NULL），同時它還是這個表的主鍵（PRIMARY KEY）。

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

如果我們想要建立一個自定義名稱的主鍵約束，可以使用表級約束：

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

表級約束在所有欄位之後定義，其中 pk_dept 是自定義的主鍵名稱。

對於數位型別的主鍵欄位，我們可以通過自增長列（auto increment）自動生成一個唯一的數位。

例如：

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

此時，我們在插入資料時不再需要為 dept_id 欄位提供資料，MySQL 預設會產生一個從 1 開始，每次遞增 1 的數位序列。

然後我們再建立兩個表：

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）是一個整數型別（INTEGER），不可以為空（NOT NULL），同時它還是這個表的主鍵（PRIMARY KEY）。
員工姓名（emp_name）是一個可變長度的字串，最長 50 個字元，不允許為空。
性別（sex）是一個可變長度的字串，最長 10 個字元，不允許為空；另外，我們通過表級約束 ck_emp_sex 限制了性別的取值只能為“男”或者“女”。
部門編號（dept_id）代表了員工所在的部門，因此通過外來鍵約束 fk_emp_dept 參照了部門表的主鍵欄位。
經理編號（manager）代表了員工的直接上級，可能為空。外來鍵約束 fk_emp_manager 表示經理也屬於員工。
入職日期（hire_date）是一個 DATE 型別的欄位，不能為空。
職位編號（job_id）代表了員工的職位，因此通過外來鍵 fk_emp_job 參照了職位表的主鍵欄位。
月薪（salary）是一個支援兩位小數的數位，不能為空。檢查約束 ck_emp_salary 要求月薪必須大於零。
獎金（bonus）是一個可選的數位欄位。
電子郵箱（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 語法只複製表結構，包括欄位的屬性和索引，但是不復制資料。

例如：

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 關鍵字。如果想要建立一個臨時表，可以使用 CREATE TEMPORARY TABLE … LIKE。

使用 mysql 表空間、InnoDB 系統表空間（innodb_system）或者通用表空間建立的表包含一個 TABLESPACE 屬性，表示該表所在的表空間。目前，無論 innodb_file_per_table 設定為什麼引數，CREATE TABLE … LIKE 都會保留 TABLESPACE 屬性。為了避免複製新表時使用原始表的 TABLESPACE 屬性，可以使用下面介紹的第三種方法。例如：

CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

以上語句會基於 orig_tbl 建立一個新的空表 new_tbl，具體參考下一節內容。

CREATE TABLE … LIKE 使用原始表的所有 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值建立目標表。

另外，LIKE 只能基於表進行復制，而不能複製檢視。

CREATE TABLE … SELECT

在 MySQL 中複製表結構的另一種方法就是利用查詢語句的結果定義欄位和複製的資料：

CREATE TABLE table_name
  [AS] SELECT ...;

其中的 SELECT 語句定義了新表的結構和資料。

以下範例基於查詢的結果建立了一個新表：emp_devp，表中包含了研發部的所有員工。

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

對於這種語法，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 中的資料：

TABLE t2;

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

如果只想複製結構，不需要複製資料，可以在查詢語句中增加 LIMIT 0 或者 WHERE 1=0 條件。

如果在 SELECT 語句前面增加 IGNORE 或者 REPLACE 關鍵字，可以處理複製資料時導致唯一鍵衝突的資料行。對於 IGNORE，源資料中和目標表重複的資料行就會被丟棄；對於 REPLACE，使用新資料行替換目標表中的已有資料行。如果沒有指定任何選項，唯一鍵衝突將會返回錯誤。

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

例如：

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

CREATE TABLE … SELECT 命令不會保留計算列的定義，也不會保留預設值定義。同時某些資料型別可能產生轉換。例如，AUTO_INCREMENT 屬性不會被保留，VARCHAR 型別被轉換為 CHAR 型別。保留的屬性包括 NULL（NOT NULL）以及 CHARACTER SET、COLLATION、COMMENT 和 DEFAULT 子句。

使用 CREATE TABLE … SELECT 命令建立表時，需要為查詢語句中的函數和表示式指定一個別名，否則該命令可能失敗或者建立意料之外的欄位名：

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 並且目標表已經存在，不會將資料複製到目標表，同時該語句不會寫入紀錄檔檔案。

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

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

總結

本文通過一些案例介紹了 MySQL 中三種建立表的方法和注意事項。

僅為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

MySQL中建立表的三種方法彙總

目錄

CREATE TABLE

CREATE TABLE … LIKE

CREATE TABLE … SELECT

總結

熱門文章