中文字幕一区二区人妻电影,亚洲av无码一区二区乱子伦as ,亚洲精品无码永久在线观看,亚洲成aⅴ人片久青草影院按摩,亚洲黑人巨大videos

MySQL中的ALTER TABLE：敵還是友？

發(fā)布于：2020-12-19 17:52:06

0

241

0

MySQL ALTER TABLE

在ALTER TABLE語句中使用最頻繁的語句之一MySQL的世界-的語句可以在表中添加，刪除或修改列。在此博客文章中，我們將嘗試更深入地了解它的用途，作用以及何時使用。

什么是ALTER TABLE，它做什么？

如上所述，ALTER TABLE語句使DBA和開發(fā)人員能夠添加，刪除或修改表中的列。簡單地說，ALTER TABLE會更改表的結(jié)構(gòu)-它使您能夠添加，刪除列，添加或刪除索引，重命名列或更改其類型。

何時以及如何使用ALTER TABLE？

為了使用ALTER TABLE，您通常需要ALTER，CREATE和INSERT特權(quán)。要重命名表，所需的特權(quán)是舊表的ALTER和DROP，然后是要創(chuàng)建的新表的CREATE，ALTER和INSERT特權(quán)。要將必需的特權(quán)分配給特定用戶，可以使用以下查詢：

1個

GRANT ALTER, CREATE, INSERT ON database.* TO 'demo_user';

如果您希望特權(quán)僅適用于某些表（通配符使特權(quán)適用于所有表），則將數(shù)據(jù)庫名替換為數(shù)據(jù)庫名，將通配符替換為表名，并將demo_user替換為用戶名。如果要在所有數(shù)據(jù)庫及其中的所有表中使用特權(quán)，只需用通配符替換數(shù)據(jù)庫：

1個

GRANT ALTER, CREATE, INSERT ON *.* TO 'demo_user';

為了實際使用ALTER TABLE語句，請運行查詢以更改表的結(jié)構(gòu)-ALTER TABLE用于添加，刪除或修改表中的列：該查詢還可以用于向列添加索引。以下是一些最常用查詢的基本示例：

1個

ALTER TABLE demo_table ADD column_name VARCHAR(255) NOT NULL DEFAULT ‘’; T

他的查詢將在列demo_table中添加一列column_name。將FIRST添加到查詢的末尾以使該列成為表中的第一列。

1個

ALTER TABLE demo_table ADD column_2 VARCHAR(255) NOT NULL DEFAULT ‘’ AFTER column_1; T

他的查詢將在表demo_table的column_1列之后添加column_2列。

1個

ALTER TABLE demo_table ADD COLUMN column_2 INT GENERATED ALWAYS AS (column_1 + 1) STORED;

該查詢會將生成的列添加到表中。

1個

ALTER TABLE demo_table DROP COLUMN demo_column;

此查詢會將列demo_column放在表demo_table上。

1個

ALTER TABLE demo_table ADD INDEX demo_index(demo_column);

該查詢將在名為demo_table的表中名為demo_column的列上添加名為demo_index（可以選擇名稱）的索引。

1個

ALTER TABLE demo_table ADD INDEX (demo_column), ADD UNIQUE (demo_unique);

該查詢將在列demo_column上添加索引，并在demo_unique列上添加唯一索引。

1個

ALTER TABLE demo_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4;

該查詢將更改特定列的默認字符集。

1個

ALTER TABLE demo_table CONVERT TO CHARACTER SET charset_name;

此查詢將更改表和所有字符（CHAR，VARCHAR和TEXT）列的默認字符集。

1個

ALTER TABLE demo_table PARTITION BY HASH(demo_column) PARTITIONS 8;

該查詢將按列將demo_column列劃分為8個分區(qū)。

1個

ALTER TABLE demo_table TABLESPACE tablespace_1 STORAGE DISK;

該查詢會將表demo_table轉(zhuǎn)換為基于磁盤的存儲。

如果要添加索引，請記住可以添加不同類型的索引（例如BTREE索引或FULLTEXT索引），也可以添加僅覆蓋查詢列中一定數(shù)量字符的索引像這樣：

1個

ALTER TABLE demo_table ADD INDEX demo_index(column_name(10));

上述查詢將增加稱為索引demo_index在柱稱為的前10個字符列名在一個叫做表demo_table。

MySQL中的索引是一個復雜的野獸，它們確實應有其自身的主題，因此我們在這里不做詳細介紹，但是如果您想了解更多信息，我們前面有關(guān)MySQL索引的文章應該提供更多的見解。

ALTER TABLE如何工作？

MySQL中的ALTER TABLE有其自身的微妙之處。MySQL的最新版本，即MySQL 8.0。有3種算法會影響ALTER TABLE進行此類更改的方式。這些是：

  • 復制

    • 在原始表的副本上執(zhí)行操作，并將表數(shù)據(jù)從原始表逐行復制到新表。在大多數(shù)情況下，就資源使用而言，此算法可能非常昂貴，尤其是對于大型表而言。選擇或選擇此算法時，不允許所有并發(fā)DML，因此，引用受影響的表的任何后續(xù)查詢都必須等待或排隊進入進程列表。如果連接最大化，您很可能會卡住數(shù)據(jù)庫。

  • 到位

    • 操作避免復制表數(shù)據(jù)，但可以在適當位置重建表。在操作的準備和執(zhí)行階段可以簡短地獲取表上的獨占元數(shù)據(jù)鎖定。通常，支持并發(fā)DML。

  • 瞬間

    • 操作僅修改數(shù)據(jù)字典中的元數(shù)據(jù)。在準備和執(zhí)行期間，不會在表上獲取任何獨占元數(shù)據(jù)鎖，并且表數(shù)據(jù)不受影響，從而使操作立即進行。允許并發(fā)DML。（在MySQL 8.0.12中引入）

對于較小的表，MySQL的ALTER TABLE過程可能不是問題，但是如果您的數(shù)據(jù)集較大，則可能會遇到問題-許多人都經(jīng)歷過ALTER TABLE查詢，這些查詢需要花費數(shù)小時，數(shù)天甚至數(shù)周的時間才能完成。在大多數(shù)情況下，發(fā)生這種情況是由于上面概述了MySQL的表更改過程。但是，有一種方法可以至少稍微減少查詢完成的時間：

  1. 通過運行，創(chuàng)建一個具有所需結(jié)構(gòu)的新表（如源表）

    1個

    CREATE TABLE demo_table_new LIKE demo_table;

    然后調(diào)整其結(jié)構(gòu)。在這種情況下，demo_table是源表，demo_table_new是新表。

  2. 將數(shù)據(jù)插入到新表中。

  3. 將舊表重命名為demo_table_old（根據(jù)需要調(diào)整名稱）。

  4. 將新表重命名為舊表的舊名稱。

  5. 最后，將行從舊表復制到新表，并在需要時創(chuàng)建索引。

盡管上述步驟可以正常工作。但是，在實際情況下，DBA或開發(fā)人員傾向于使用Percona的pt-online-schema-change或Github的gh-ost。您可以查看我們以前的文章《 MySQL和MariaDB遷移的頂級開源工具》，其中概述了這些架構(gòu)更改工具。


無論如何，我們上面所描述的通常被稱為“影子副本”方法：本質(zhì)上，您使用所需的結(jié)構(gòu)構(gòu)建了一個新表，然后執(zhí)行重命名和刪除操作以交換這兩個表。還有另一種方式：您還可以交換服務器，并在非生產(chǎn)環(huán)境中的服務器上運行ALTER TABLE。對于MyISAM，您可以先禁用密鑰，加載數(shù)據(jù)，然后啟用密鑰。


ALTER TABLE陷阱

如果您使用ALTER TABLE語句創(chuàng)建索引（也可以使用CREATE INDEX語句），建議在插入數(shù)據(jù)后創(chuàng)建索引，因為這是一種不僅在MySQL中而且在加快處理速度方面眾所周知的方法在其他數(shù)據(jù)庫管理系統(tǒng)中，例如Oracle。不過，一般而言，請記住，大多數(shù)ALTER TABLE操作都應引起MySQL的某些問題（服務中斷）。

盡管還有一點高級，但是還有另一種方法可以加快整個過程的速度：如果您可以說服MySQL僅修改表的.frm文件（.frm文件描述表的定義），而不管表，過程會更快：

  1. 創(chuàng)建一個具有與舊表相同布局的空表，而無需對其進行修改。

  2. 關(guān)閉正在使用的所有表，并防止通過運行打開所有新表 

    1個

    FLUSH TABLES WITH READ LOCK.

  3. 交換.frm文件。

  4. 通過運行UNLOCK TABLES釋放讀取鎖。

還請記住，如果您要修改列并且語法似乎正確，但是仍然出現(xiàn)錯誤，那么可能是時候考慮使用其他語法了。例如：

1個

ALTER TABLE demo_table ADD long VARCHAR(255);

這樣的查詢會出錯，因為long是保留字。為了避免此類錯誤，請使用反引號將單詞轉(zhuǎn)義：

1個

ALTER TABLE demo_table ADD `long` VARCHAR(255);

還值得注意的是，列名只能使用反引號進行轉(zhuǎn)義，而不能使用單引號或雙引號進行轉(zhuǎn)義。例如，這樣的查詢也會出錯：

1個

ALTER TABLE demo_table CHANGE COLUMN ‘demo_column’ ‘demo_column_2’ VARCHAR(255);

概要

MySQL使用ALTER TABLE語句添加，刪除或修改表中的列。為了成功執(zhí)行該語句，您必須對表具有ALTER，CREATE和INSERT特權(quán)。該語句還具有一些獨特的微妙之處：由于它的工作方式而在非常大的表上運行時，其性能可能會受到影響，但是只要您知道該語句的工作方式和作用，就可以了。