MySQL ALTER命令使用详解

2024-03-18 16:08:03 91

ALTERTABLE允许你修改一个现有表的结构。例如，你可以增加或删除列、创造或消去索引、改变现有列的类型、或重新命名列或表本身。你也能改变表的注释和表的类型。
你可以使用CHANGEold_col_namecreate_definition子句重命名一个列。为了这样做，指定旧的和新的列名字和列当前有的类型。例如，重命名一个INTEGER列，从a到b，你可以这样做：
mysql>ALTERTABLEt1CHANGEabINTEGER;
如果你想要改变列的类型而非名字，就算他们是一样的，CHANGE语法仍然需要2个列名。例如：
mysql>ALTERTABLEt1CHANGEbbBIGINTNOTNULL;
然而，在MySQL3.22.16a，你也可以使用MODIFY来改变列的类型而不是重命名它：
mysql>ALTERTABLEt1MODIFYbBIGINTNOTNULL;
如果你使用CHANGE或MODIFY缩短一个列，一个索引存在于该列的部分(例如，如果你有一个VARCHAR列的头10个字符的索引)，你不能使列短于被索引的字符数目。
ALTERCOLUMN为列指定新的缺省值或删除老的缺省值。如果老的缺省值被删除且列可以是NULL，新缺省值是NULL。如果列不能是NULL，MySQL赋予一个缺省值。缺省值赋值在7.7CREATETABLE句法中描述。
DROPINDEX删除一个索引。这是MySQL对ANSISQL92的一个扩充。
如果列从一张表中被丢弃，列也从他们是组成部分的任何索引中被删除。如果组成一个索引的所有列被丢弃，该索引也被丢弃。
DROPPRIMARYKEY丢弃主索引。如果这样的索引不存在，它丢弃表中第一个UNIQUE索引。(如果没有明确地指定PRIMARYKEY，MySQL标记第一个UNIQUE键为PRIMARYKEY。）
用CAPI函数mysql_info()，你能找出多少记录被拷贝，和(当使用IGNORE时)由于唯一键值的重复多少记录被删除。
这里是一个例子，显示了一些ALTERTABLE用法。我们以一个如下

/*创建的表t1开始：
mysql>CREATETABLEt1(aINTEGER,bCHAR(10));
重命名表，从t1到t2:
mysql>ALTERTABLEt1RENAMEt2;
为了改变列a，从INTEGER改为TINYINTNOTNULL(名字一样)，并且改变列b，

从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c：
mysql>ALTERTABLEt2MODIFYaTINYINTNOTNULL,CHANGEbcCHAR(20);
增加一个新TIMESTAMP列，名为d：
mysql>ALTERTABLEt2ADDdTIMESTAMP;
在列d上增加一个索引，并且使列a为主键：
mysql>ALTERTABLEt2ADDINDEX(d),ADDPRIMARYKEY(a);
删除列c：
mysql>ALTERTABLEt2DROPCOLUMNc;
增加一个新的AUTO_INCREMENT整数列，命名为c：
mysql>ALTERTABLEt2ADDcINTUNSIGNEDNOTNULLAUTO_INCREMENT,
ADDINDEX(c);*/

注意，这里索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOTNULL，因为索引了的列不能是NULL。当你增加一个AUTO_INCREMENT列时，自动地用顺序数字填入列值。

具体实例分析：

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQLALTER命令。

开始本章教程前让我们先创建一张表，表名为：testalter_tbl。

root@host#mysql-uroot-ppassword;
Enterpassword:*******
mysql>useTUTORIALS;
Databasechanged
mysql>createtabletestalter_tbl
->(
->iINT,
->cCHAR(1)
->);
QueryOK,0rowsaffected(0.05sec)
mysql>SHOWCOLUMNSFROMtestalter_tbl;
+-------+---------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+---------+------+-----+---------+-------+
|i|int(11)|YES||NULL||
|c|char(1)|YES||NULL||
+-------+---------+------+-----+---------+-------+
2rowsinset(0.00sec)

删除，添加或修改表字段
如下命令使用了ALTER命令及DROP子句来删除以上创建表的i字段：

mysql>ALTERTABLEtestalter_tbl DROPi;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MySQL中使用ADD子句来想数据表中添加列，如下实例在表testalter_tbl中添加i字段，并定义数据类型:

mysql>ALTERTABLEtestalter_tblADDiINT;
执行以上命令后，i字段会自动添加到数据表字段的末尾。

mysql>SHOWCOLUMNSFROMtestalter_tbl;
+-------+---------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+---------+------+-----+---------+-------+
|c|char(1)|YES||NULL||
|i|int(11)|YES||NULL||
+-------+---------+------+-----+---------+-------+
2rowsinset(0.00sec)

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字FIRST(设定位第一列)，AFTER字段名（设定位于某个字段之后）。

尝试以下ALTERTABLE语句,在执行成功后，使用SHOWCOLUMNS查看表结构的变化：

ALTERTABLEtestalter_tblDROPi;
ALTERTABLEtestalter_tblADDiINTFIRST;
ALTERTABLEtestalter_tblDROPi;
ALTERTABLEtestalter_tblADDiINTAFTERc;

FIRST和AFTER关键字只占用于ADD子句，所以如果你想重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD来添加字段并设置位置。

修改字段类型及名称
如果需要修改字段类型及名称,你可以在ALTER命令中使用MODIFY或CHANGE子句。

例如，把字段c的类型从CHAR(1)改为CHAR(10)，可以执行以下命令:

mysql>ALTERTABLEtestalter_tblMODIFYcCHAR(10);
使用CHANGE子句,语法有很大的不同。在CHANGE关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：

mysql>ALTERTABLEtestalter_tblCHANGEijBIGINT;
mysql>ALTERTABLEtestalter_tblCHANGEjjINT;

ALTERTABLE对Null值和默认值的影响
当你修改字段时，你可以指定是否包含只或者是否设置默认值。

以下实例，指定字段j为NOTNULL且默认值为100。

mysql>ALTERTABLEtestalter_tbl
->MODIFYjBIGINTNOTNULLDEFAULT100;

如果你不设置默认值，MySQL会自动设置该字段默认为NULL。

修改字段默认值
你可以使用ALTER来修改字段的默认值，尝试以下实例：

mysql>ALTERTABLEtestalter_tblALTERiSETDEFAULT1000;
mysql>SHOWCOLUMNSFROMtestalter_tbl;
+-------+---------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+---------+------+-----+---------+-------+
|c|char(1)|YES||NULL||
|i|int(11)|YES||1000||
+-------+---------+------+-----+---------+-------+
2rowsinset(0.00sec)

你也可以使用ALTER命令及DROP子句来删除字段的默认值，如下实例：

mysql>ALTERTABLEtestalter_tblALTERiDROPDEFAULT;
mysql>SHOWCOLUMNSFROMtestalter_tbl;
+-------+---------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+---------+------+-----+---------+-------+
|c|char(1)|YES||NULL||
|i|int(11)|YES||NULL||
+-------+---------+------+-----+---------+-------+
2rowsinset(0.00sec)
ChangingaTableType:

修改数据表类型，可以使用ALTER命令及TYPE子句来完成。尝试以下实例，我们将表testalter_tbl的类型修改为MYISAM：

注意：查看数据表类型可以使用SHOWTABLESTATUS语句。

mysql>ALTERTABLEtestalter_tblTYPE=MYISAM;
mysql>SHOWTABLESTATUSLIKE'testalter_tbl'\G
***************************1.row****************
Name:testalter_tbl
Type:MyISAM
Row_format:Fixed
Rows:0
Avg_row_length:0
Data_length:0
Max_data_length:25769803775
Index_length:1024
Data_free:0
Auto_increment:NULL
Create_time:2007-06-0308:04:36
Update_time:2007-06-0308:04:36
Check_time:NULL
Create_options:
Comment:
1rowinset(0.00sec)

修改表名
如果需要修改数据表的名称，可以在ALTERTABLE语句中使用RENAME子句来实现。

尝试以下实例将数据表testalter_tbl重命名为alter_tbl：

mysql>ALTERTABLEtestalter_tblRENAMETOalter_tbl;

以上就是本文的全部内容，希望对大家的学习有所帮助。

MySQL ALTER命令使用详解

热门推荐

随机推荐