MySQL-ALTER命令

MySQL-ALTER命令 (MySQL - ALTER Command)

The MySQL ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.

当您要更改表的名称，任何表字段或要添加或删除表中的现有列时，MySQL ALTER命令非常有用。

Let us begin with the creation of a table called testalter_tbl.

让我们从创建一个名为testalter_tbl的表开始 。

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table testalter_tbl
   -> (
   -> i INT,
   -> c CHAR(1)
   -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   i   | int(11) | YES  |     |   NULL  |       |
|   c   | char(1) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除，添加或重新定位列 (Dropping, Adding or Repositioning a Column)

If you want to drop an existing column i from the above MySQL table, then you will use the DROP clause along with the ALTER command as shown below −

如果要从上面MySQL表中删除现有列i，则将使用DROP子句以及ALTER命令，如下所示-

mysql> ALTER TABLE testalter_tbl  DROP i;

A DROP clause will not work if the column is the only one left in the table.

如果列是表中剩下的唯一列，则DROP子句将不起作用。

To add a column, use ADD and specify the column definition. The following statement restores the i column to the testalter_tbl −

要添加列，请使用ADD并指定列定义。 以下语句将i列还原到testalter_tbl-

mysql> ALTER TABLE testalter_tbl ADD i INT;

After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have the same structure. This is because there are new columns that are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.

发出此语句后，testalter将包含与您第一次创建表时相同的两列，但结构不会相同。 这是因为默认情况下，有新列添加到表的末尾。 因此，即使我最初是mytbl中的第一列，但现在却是最后一列。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after the col_name.

要表明您希望在表中的特定位置上放置一列，请使用FIRST将其设为第一列，或者使用AFTER col_name指示将新列放置在col_name之后。

Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has −

尝试以下ALTER TABLE语句，在每个语句之后使用SHOW COLUMNS来查看每个语句有什么作用-

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

FIRST和AFTER说明符仅与ADD子句一起使用。 这意味着，如果要重新定位表中的现有列，则必须先删除它，然后再将其添加到新位置。

更改(更改)列定义或名称 (Altering (Changing) a Column Definition or a Name)

To change a column's definition, use MODIFY or CHANGE clause along with the ALTER command.

要更改列的定义，请使用MODIFY或CHANGE子句以及ALTER命令。

For example, to change column c from CHAR(1) to CHAR(10), you can use the following command −

例如，要将列c从CHAR(1)更改为CHAR(10)，可以使用以下命令-

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name.

使用CHANGE ，语法有些不同。 在CHANGE关键字之后，为要更改的列命名，然后指定新定义，其中包括新名称。

Try out the following example −

尝试以下示例-

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

If you now use CHANGE to convert j from BIGINT back to INT without changing the column name, the statement will be as shown below −

如果现在使用CHANGE在不更改列名的情况下将j从BIGINT转换回INT ，该语句将如下所示-

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

The Effect of ALTER TABLE on Null and Default Value Attributes − When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is. In fact, if you don't do this, MySQL automatically assigns values for these attributes.

ALTER TABLE对空值和默认值属性的影响 -修改或更改列时，您还可以指定该列是否可以包含NULL值及其默认值。 实际上，如果您不这样做，MySQL会自动为这些属性分配值。

The following code block is an example, where the NOT NULL column will have the value as 100 by default.

下面的代码块是一个示例，其中NOT NULL列的默认值将为100。

mysql> ALTER TABLE testalter_tbl 
   -> MODIFY j BIGINT NOT NULL DEFAULT 100;

If you don't use the above command, then MySQL will fill up NULL values in all the columns.

如果您不使用上述命令，则MySQL将在所有列中填充NULL值。

更改(更改)列的默认值 (Altering (Changing) a Column's Default Value)

You can change a default value for any column by using the ALTER command.

您可以使用ALTER命令更改任何列的默认值。

Try out the following example.

试试下面的例子。

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   1000  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You can remove the default constraint from any column by using DROP clause along with the ALTER command.

您可以使用DROP子句以及ALTER命令从任何列中删除默认约束。

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改(更改)表格类型 (Altering (Changing) a Table Type)

You can use a table type by using the TYPE clause along with the ALTER command. Try out the following example to change the testalter_tbl to MYISAM table type.

您可以通过将TYPE子句与ALTER命令一起使用来使用表类型。 尝试以下示例将testalter_tbl更改为MYISAM表类型。

To find out the current type of a table, use the SHOW TABLE STATUS statement.

要找出表的当前类型，请使用SHOW TABLE STATUS语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE '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-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名(更改)表 (Renaming (Altering) a Table)

To rename a table, use the RENAME option of the ALTER TABLE statement.

要重命名表，请使用ALTER TABLE语句的RENAME选项。

Try out the following example to rename testalter_tbl to alter_tbl.

尝试以下示例将testalter_tbl重命名为alter_tbl 。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

You can use the ALTER command to create and drop the INDEX command on a MySQL file. We will discuss in detail about this command in the next chapter.

您可以使用ALTER命令在MySQL文件上创建和删除INDEX命令。 在下一章中，我们将详细讨论该命令。

翻译自: https://www.tutorialspoint.com/mysql/mysql-alter-command.htm