Mysqli 简明教程

MySQLi - ALTER Command

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

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.

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

Let’s begin with creation of a table called tutorials_alter.

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

mysql> use TUTORIALS;
Database changed

mysql> create table tutorials_alter
   → (
   → i INT,
   → c CHAR(1)
   → );
Query OK, 0 rows affected (0.27 sec)

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

Dropping, Adding or Repositioning a Column

假设您想放弃上面 MySQL 表的现有列 i ，那么您将按照如下所示，使用 DROP 子句和 ALTER 命令 −

Suppose you want to drop an existing column i from above MySQL table then you will use DROP clause along with ALTER command as follows −

mysql> ALTER TABLE tutorials_alter  DROP i;

当列是表格中仅剩的一列时， DROP 不会起作用。

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

要添加列，请使用 ADD 并指定列定义。以下语句将 i 列还原到教程_更改 −

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

mysql> ALTER TABLE tutorials_alter ADD i INT;

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

After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have quite the same structure. That’s because new columns 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.

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

要指示您要在表的特定位置放置一列，请使用 FIRST 使其成为第一列，或使用 AFTER col_name 指示将新列置于 col_name 之后。试用一下以下 ALTER TABLE 语句，并在每个语句后使用 SHOW COLUMNS 来查看每个语句有什么效果 −

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 col_name. Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has −

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;

FIRST 和 AFTER 限定符仅适用于 ADD 子句。这意味着如果您想重新定位表中现有的列，则必须先将其 DROP，然后在新的位置将其 ADD。

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.

Changing a Column Definition or Name

要更改列的定义，请使用 MODIFY 或 CHANGE 子句以及 ALTER 命令。例如，要将列 c 从 CHAR(1) 更改为 CHAR(10)，请这样做 −

To change a column’s definition, use MODIFY or CHANGE clause along with ALTER command. For example, to change column c from CHAR(1) to CHAR(10), do this −

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

使用 CHANGE 时，语法有些不同。在 CHANGE 关键字后面，您命名要更改的列，然后指定新定义，其中包括新名称。试用以下示例：

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. Try out the following example:

mysql> ALTER TABLE tutorials_alter CHANGE i j BIGINT;

如果您现在使用 CHANGE 将 j 从 BIGINT 转换回 INT 而无需更改列名，则语句将按预期的那样 −

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

mysql> ALTER TABLE tutorials_alter CHANGE j j INT;

The Effect of ALTER TABLE on Null and Default Value Attributes −

在 MODIFY 或 CHANGE 列时，您还可以指定列是否可以包含 NULL 值以及其默认值是什么。事实上，如果不这样做，MySQL 会自动为这些属性分配值。

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.

以下示例中，NOT NULL 列的默认值为 100。

Here is the example, where NOT NULL column will have value 100 by default.

mysql> ALTER TABLE tutorials_alter
   → MODIFY j BIGINT NOT NULL DEFAULT 100;

如果您不使用上述命令，那么 MySQL 将填入所有列中的 NULL 值。

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

Changing a Column’s Default Value

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

You can change a default value for any column using ALTER command. Try out the following example.

mysql> ALTER TABLE tutorials_alter ALTER j SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | 1000    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

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

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

mysql> ALTER TABLE tutorials_alter ALTER j DROP DEFAULT;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

Changing a Table Type

您可以使用 TYPE 子句和 ALTER 命令使用表类型。

You can use a table type by using TYPE clause along with ALTER command.

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

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

mysql>  SHOW TABLE STATUS LIKE 'tutorials_alter'\G
*************************** 1. row ***************************
           Name: tutorials_alter
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-02-17 11:30:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Renaming a Table

要重命名表，请使用 ALTER TABLE 语句的 RENAME 选项。试用以下示例将 tutorials_alter 重命名为 tutorials_bks。

To rename a table, use the RENAME option of the ALTER TABLE statement. Try out the following example to rename tutorials_alter to tutorials_bks.

mysql> ALTER TABLE tutorials_alter RENAME TO tutorials_bks;

您可以使用 ALTER 命令在 MySQL 文件中创建和删除 INDEX。我们将在下一章中看到此功能。

You can use ALTER command to create and drop INDEX on a MySQL file. We will see this feature in next chapter.