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

MySQL中的ALTER TABLE：敵還是友？

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

0

232

0

MySQL ALTER TABLE

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

什么是ALTER TABLE，它做什么？

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

何時(shí)以及如何使用ALTER TABLE？

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

1個(gè)

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

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

1個(gè)

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

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

1個(gè)

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

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

1個(gè)

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

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

1個(gè)

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

該查詢會(huì)將生成的列添加到表中。

1個(gè)

ALTER TABLE demo_table DROP COLUMN demo_column;

此查詢會(huì)將列demo_column放在表demo_table上。

1個(gè)

ALTER TABLE demo_table ADD INDEX demo_index(demo_column);

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

1個(gè)

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

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

1個(gè)

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

該查詢將更改特定列的默認(rèn)字符集。

1個(gè)

ALTER TABLE demo_table CONVERT TO CHARACTER SET charset_name;

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

1個(gè)

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

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

1個(gè)

ALTER TABLE demo_table TABLESPACE tablespace_1 STORAGE DISK;

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

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

1個(gè)

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

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

MySQL中的索引是一個(gè)復(fù)雜的野獸，它們確實(shí)應(yīng)有其自身的主題，因此我們?cè)谶@里不做詳細(xì)介紹，但是如果您想了解更多信息，我們前面有關(guān)MySQL索引的文章應(yīng)該提供更多的見(jiàn)解。

ALTER TABLE如何工作？

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

  • 復(fù)制

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

  • 到位

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

  • 瞬間

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

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

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

    1個(gè)

    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. 最后，將行從舊表復(fù)制到新表，并在需要時(shí)創(chuàng)建索引。

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


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


ALTER TABLE陷阱

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

盡管還有一點(diǎn)高級(jí)，但是還有另一種方法可以加快整個(gè)過(guò)程的速度：如果您可以說(shuō)服MySQL僅修改表的.frm文件（.frm文件描述表的定義），而不管表，過(guò)程會(huì)更快：

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

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

    1個(gè)

    FLUSH TABLES WITH READ LOCK.

  3. 交換.frm文件。

  4. 通過(guò)運(yùn)行UNLOCK TABLES釋放讀取鎖。

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

1個(gè)

ALTER TABLE demo_table ADD long VARCHAR(255);

這樣的查詢會(huì)出錯(cuò)，因?yàn)閘ong是保留字。為了避免此類錯(cuò)誤，請(qǐng)使用反引號(hào)將單詞轉(zhuǎn)義：

1個(gè)

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

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

1個(gè)

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

概要

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