MariaDB修改表

ALTER TABLE命令是用于更改現(xiàn)有表的結(jié)構(gòu)。 例如，可以添加或刪除列，創(chuàng)建或銷毀索引，更改現(xiàn)有列的類型或重命名列或表本身。也可以更改表的注釋和表的存儲引擎。

如果另一個連接正在使用該表，則元數(shù)據(jù)鎖將處于活動狀態(tài)，并且此語句將等待，直到釋放該鎖。對于非事務(wù)性表也是如此。

在具有重復(fù)值的列(或一組列)上添加一個UNIQUE索引時，將會產(chǎn)生一個錯誤，并且該語句將被停止。要抑制錯誤并強(qiáng)制創(chuàng)建UNIQUE索引，丟棄重復(fù)項，可以指定IGNORE選項。 如果一列(或一組列)應(yīng)該是唯一的，但是它包含重復(fù)的值，這有時可能是有用的; 然而，這種技術(shù)不能控制哪些行被保存，哪些被刪除。

1. 添加一列

要在原有的表上添加一列，可參考以下語法 -

ALTER TABLE xxxx ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)

示例

CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;

INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);

ALTER TABLE t ADD COLUMN
(d DATETIME DEFAULT current_timestamp(),
 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'),
 t TEXT CHARSET utf8 DEFAULT 'The quick brown fox jumps over the lazy dog');

UPDATE t SET t=NULL WHERE id=3;

SELECT id,u,d,ST_AsText(p),t FROM t;

SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column';

2. 刪除一列

語法

ALTER TABLE xxxx DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]

刪除屬于多列UNIQUE約束的列是不允許的。例如：

CREATE TABLE a (
  a int,
  b int,
  primary key (a,b)
);

ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in table

原因是，刪除列a將導(dǎo)致新的約束，即列b中的所有值都是唯一的。 為了刪除該列，將需要明確的DROP PRIMARY KEY和ADD PRIMARY KEY。從MariaDB 10.2.7版本起，該列被刪除，并應(yīng)用了額外的約束，導(dǎo)致以下結(jié)構(gòu)：

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

3. MODIFY COLUMN修改列

允許修改列的類型。該列與原始列位于同一位置，列中的所有索引都將保留。 請注意，修改列時，應(yīng)指定新列的所有屬性。

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;

除了改變列的名字。該列與原始列位于同一位置，列中的所有索引都將保留。

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));
ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;

4. 添加約束 - ADD CONSTRAINT

修改在表的特定列或列上添加約束。

語法

ALTER TABLE table_name 
ADD CONSTRAINT [constraint_name] CHECK(expression);

在插入或更新行之前，所有約束都將按照它們定義的順序進(jìn)行評估。如果任何約束失敗，那么該行將不會被更新。

示例

CREATE TABLE account_ledger (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_name VARCHAR(100),
    credit_account VARCHAR(100),
    credit_amount INT,
    debit_account VARCHAR(100),
    debit_amount INT);

ALTER TABLE account_ledger 
ADD CONSTRAINT is_balanced 
    CHECK((debit_amount + credit_amount) = 0);

刪除DROP CONSTRAINT

修改表結(jié)構(gòu)，刪除給定的約束。

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

向表中添加約束時，無論是通過CREATE TABLE還是ALTER TABLE ... ADD CONSTRAINT語句，都可以自己設(shè)置constraint_name，或者允許MariaDB自動為您生成一個約束。例如，

CREATE TABLE t (
   a INT,
   b INT,
   c INT,
   CONSTRAINT CHECK(a > b),
   CONSTRAINT check_equals CHECK(a = c)); 

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 't';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| check_equals    | t              | CHECK           |
| CONSTRAINT_1    | t              | CHECK           |
+-----------------+----------------+-----------------+

要從表中刪除約束，請使用ALTER TABLE ... DROP CONSTRAINT語句。 例如，

ALTER TABLE t DROP CONSTRAINT is_unique;

4. 常用語句

添加一個新的列：

ALTER TABLE t1 ADD x INT;

刪除一個列：

ALTER TABLE t1 DROP x;

修改列的類型：

ALTER TABLE t1 MODIFY x bigint unsigned;

更改列的名稱和類型：

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

更改列的名稱和類型：

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

在單個ALTER TABLE語句中組合多個子句，用逗號分隔：

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

更改存儲引擎：

ALTER TABLE t1 ENGINE = InnoDB;

重建表(前面的例子也將重建表，如果它已經(jīng)是InnoDB)：

ALTER TABLE t1 FORCE;