What does the alter table command do? MySQL Reference Guide. Creating standalone indexes

If your project has tables whose size amounts to gigabytes, and in order to change the structure of such a table you have to stop all services for several hours - this article will be for you.

Given: a table of several tens of gigabytes of data in size. The task is to change the structure of the table.

Let me jump right in, the method will only work on transactional tables. If you have a MyISAM table of tens of gigabytes, then it’s like in that joke - “deal with your problems yourself.” An example will be given for an InnoDB table.

Let's assume that the structure of our table is like this:

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
We want to add the last_login field to this table.

What options do we have?

Head-on

ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
This option works great on small projects where the table size rarely exceeds 50,000 records. The option does not suit us because... ALTER will take too long to execute and all this time the table will be locked for both writing and reading. Accordingly, the service will need to be stopped for this time.

Turn on the brain

You can not touch the table at all, for that matter, but make a separate `users_lastvisits`:
CREATE TABLE `users_lastvisits` (`user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now you can make a JOIN with the last_login table in all queries where last_login is needed. It will, of course, work slower, and adding JOIN to queries will also take extra time, but in general this is sometimes enough and you can stop at this point.

And yet - you need to add a field

You can raise master-slave replication, do ALTER on the slave server and then swap them. To be honest, I have never done this, maybe it is simpler than the next method, but it is not always possible to increase replication.

My method is as follows

We create a new table with a final structure, create triggers on the first table that will log all changes, at the same time we begin to pour data from the first table into the second, and when finished we “pour in” the changed data and rename the tables.

So, we prepare 2 tables - the first with the required structure, the second for logging changes.
CREATE TABLE `_users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT "0", `action` enum("updated","deleted") NOT NULL DEFAULT "updated" , PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Set up triggers:
DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); ENDIF; INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; DELIMITER ;
Now let's start pouring. To do this, you need to open 2 connections to the database. In one there will be a proper overflow, in the other it will be necessary to briefly block the table for writing.
mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> -- triggers should already be installed mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> -- in another console we start the overflow mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> -- again in the first console mysql> UNLOCK TABLES;
That's it, now while the table is being poured, we have time to think about how we will pour in the data that has changed since the start of the pouring. There’s actually nothing complicated here - I won’t provide the script, you just need to take one record at a time from the users_updated_rows table in the order in which they were added (sort by primary key) and update or delete it in the _users table;

So, the table reloading has already finished, we need to pour in the remaining data. Let's run the script. The script should run constantly and update all entries that are added to the log; when it fills all the data, you need to rename the tables:
mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)
It is worth noting that at this moment a slight loss of data is possible. requests are not executed atomically. If this is critical, it is better to turn off the service for a while so that there are no requests for changes. You can, for example, take away write rights from a user and execute commands under another user.

If everything is done correctly, no data will be lost and there will be virtually no interruptions in the service. Which is exactly what we needed. In the same way, you can transfer data to another server, only the transfer method will change. Instead of
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
you need to pour through mysqldump:
$ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2
In this way, I was able to transfer a table of 60Gb in size and 400 million rows to another server without stopping the operation of services in about 12 hours.

By the way, the bicycle has already been invented by Facebook and is called

Modifies the table structure in a specified way.

Syntax:

ALTER TABLE TableName1 ADD | ALTER FieldName1 FieldType [(nFieldWidth [, nPrecision])] ] ] - Or - ALTER TABLE TableName1 ALTER FieldName2 ] - Or - ALTER TABLE TableName1 FieldName3] ] ] TAG TagName4 REFERENCES TableName2 ] ]

Options:
TableName1
Specifies the name of the table whose structure is being modified.
ADD FieldName1
Specifies the name of the field to be added.
ALTER FieldName1
Specifies the name of the existing field to be modified.
FieldType [(nFieldWidth [, nPrecision]])
Specifies the field type, field width, and field precision (number of decimal places) for a new or modified field.
FieldType it is a single letter indicating the data type of the field. Some field types require you to specify nFieldWidth and/or nPrecision.
The following table lists the parameter values FieldType; for each of them it is indicated whether parameters are required nFieldWidth And nPrecision.

FieldType nFieldWidth nPrecision Description C n - Character field of width n D - - Date T - - DateTime field N n d Numeric field of width n, having d decimal places F n d Floating point data field of width n, having d decimal places I - - Field integers B - d Double precision data field Y - - Currency field L - - Boolean M - - Memo field G - - General field

Options nFieldWidth And nPrecision are ignored in the case of types D, T, I, Y, L, M, G and P. If for type N, F or B the value nPrecision not specified, it is assumed to be zero by default (no decimal places).
NULL
Allows a field to accept values NULL.
NOT NULL
Prevents a field from accepting values NULL.
If sentences are omitted NULL And NOT NULL, then the admissibility of values NULL for the field is determined by the current setting SET NULL. However, if we omit the sentences NULL And NOT NULL and ask PRIMARY KEY or UNIQUE, current installation SET NULL will be ignored and the default for the field will be set to NOT NULL.
CHECK lExpression1
Specifies the field level validity rule. Calculation lExpression1 should give logical expression; it can be a user-defined function or a stored procedure. Please note that this rule is checked when adding an empty record. If this rule does not allow empty field values in the attached record, an error is generated.
ERROR cMessageText1
Specifies the text of the error message that is displayed when an error is detected according to a field rule. This message appears only when you change data in the view window or edit window.
DEFAULT eExpression1
Sets the default value for the field. Expression data type eExpression1 must match the data type of the field.
PRIMARY KEY
Creates a primary index tag. The tag name is the same as the field name.
UNIQUE
Creates a candidate index tag with the same name as the field. For more information about candidate indexes, see Chapter 7, Working with Tables, in the Developer's Guide.
Note Candidate indexes (created using the UNIQUE included in the commands ALTER TABLE And CREATE TABLE for compatibility with ANSI) are different from indexes created using the option UNIQUE teams INDEX. Index created by option UNIQUE in a team INDEX, allows duplicate index keys; Duplicate keys are not allowed in candidate indexes.
The field used for the primary index or candidate index does not allow values NULL and duplicate entries.
If you create a new field using the ADD COLUMN clause, Visual FoxPro will not generate an error when you create a primary or candidate index on a field that supports null values. However, if you try to enter a null or duplicate value in a field that is used for a primary index or a candidate index, Visual FoxPro will give an error.
If you are modifying an existing field and the primary index or candidate index expression consists of only the name of that one field, Visual FoxPro will check if the field contains any values NULL or duplicate entries. If such values are found, Visual FoxPro will generate an error and the table remains unchanged. If the index expression contains multiple fields or the expression includes a single field, Visual FoxPro will not check for values NULL or duplicate entries.
REFERENCES TableName2 TAG TagName1
Specifies the parent table with which to establish a persistent relationship. TAG TagName1 specifies the index tag of the parent table on which this relationship is based. The index tag name can be up to 10 characters long.
NOCPTRANS
Prohibits translation to another code page for character and memo fields. If the table is converted to another code table, fields with the NOCPTRANS option do not participate in translation. NOCPTRANS can only be set for character fields and memo fields.
IN following example a table is created with the name MYTABLE, which contains two character fields and two memo fields. The second character field char2 and the second memo field memo2 are defined with the option NOCPTRANS to prevent broadcasting.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2, M NOCPTRANS)

ALTER FieldName2
Specifies the name of the existing field to be modified.
SET DEFAULT eExpression2
Specifies a new value to be the default for an existing field. Expression data type eExpression2 must match the data type defined for the field.
SET CHECK lExpression2
Specifies a new rule for an existing field. Calculation lExpression2 should give a boolean value; it can be a user-defined function or a stored procedure.
ERROR cMessageText2
Specifies the text of the error message. This message will be displayed when an error is detected according to the field level rule. The message is issued only when there is a change in data in the viewing window or editing window.
DROP DEFAULT
Overrides the default value for an existing field.
DROP CHECK
Cancels a rule for an existing field.
DROP FieldName3
Specifies the field to be removed from the table. Removing a field from a table also clears the default value for that field.
If a field specifies an index key expression or a trigger expression, those expressions become invalid when the field is deleted. In this case, no error will be generated when the field is deleted, but errors will be raised for invalid index key or trigger expressions at runtime.
SET CHECK lExpression3
Specifies a table-level rule. Calculation lExpression3 must produce a Boolean expression, it can be a user defined function or a stored procedure.
ERROR cMessageText3
Specifies the text of the error message that is displayed when an error is detected according to a table-level rule. This message appears only when you change data in the view window or edit window.
DROP CHECK
Overrides a table level rule.
ADD PRIMARY KEY eExpression3 TAG TagName2
Adds a primary index to the table. eExpression3 specifies the primary index key expression, and TagName2 primary index tag name. The index tag name can be up to 10 characters long. If TAG TagName2 omitted, and eExpression3 defines a single field, the primary index tag is given the same name as the field specified in the expression eExpression3.
DROP PRIMARY KEY
Removes the primary index and its tag. Since a table can only have one primary key, its name can be omitted. When you drop a primary index, all persistent relationships based on the primary key are also deleted.
ADD UNIQUE eExpression4
Adds a candidate index to the table. eExpression4 specifies the candidate index key expression, and TagName3 the name of the candidate index tag. The index tag name can be up to 10 characters long. If the expression TAG TagName3 omitted, and eExpression4 defines a single field, the candidate index tag is given the same name as the field specified in the expression eExpression4.
DROP UNIQUE TAG TagName4
Removes the candidate index and its tag. Because a table can have multiple candidate keys, you must specify the name of the candidate index tag to delete.
ADD FOREIGN KEY TAG TagName4
Adds an external (other than primary) index to the table. eExpression5 specifies the outer index key expression, and TagName4 external index tag name. The index tag name can be up to 10 characters long.
REFERENCES TableName2
Specifies the parent table with which to establish a persistent relationship. To establish a relationship with a parent table based on an existing index tag, you must specify TAG TagName5. The index tag name can be up to 10 characters long. If TAG TagName5 omitted, the relationship is established using the parent table's primary index tag.
DROP FOREIGN KEY TAG TagName6
Deletes external key, whose index tag is named TagName6. If you omit SAVE, the index tag will be removed from the structure index. Turning on SAVE, you will prevent the tag from being removed from the structure index.
RENAME COLUMN FieldName4 TO FieldName5
Allows you to change the name of a field in a table. FieldName4 specifies the name of the field that should be changed. FieldName5 specifies a new field name.
Caution Be careful when renaming table fields, index expressions, field and table validation rules, commands, functions, etc. may continue to refer to the old field names.
NOVALIDATE
Indicates that Visual FoxPro allows changes to be made to the table structure that may violate the integrity of the tabular data. Default Visual FoxPro prohibits the team ALTER TABLE make such destructive changes to the structure. Enabling the option NOVALIDATE allows this ban to be lifted.

Table manipulation is one of the most common activities that database administrators and programmers perform when working with database objects. This section details how to create and modify tables.

The ANSI standard is something of a lowest common denominator for all manufacturers, although not every possibility standard version The CREATE TABLE and ALTER TABLE statements are implemented by each manufacturer. However, the ANSI standard is a basic form that can be used on all platforms.

In turn, the platforms offer various extensions and additions to the ANSI standard CREATE TABLE and ALTER TABLE statements.

Typically, you need to think carefully about the design of the table and how to create it. This process is called database design. The process of analyzing a table's relationships with its own data and other tables in the database is called normalization. We recommend that programmers and database administrators study design and normalization principles before using CREATE DATABASE commands.

SQL 2003 syntax

When you run the SQL 2003 CREATE TABLE statement, a permanent or temporary table is created in the database. The syntax is as follows.

Keywords

TEMPORARY

A permanent or TEMPORARY table with local (LOCAL) or global (GLOBAL) scope is declared. Local temporary tables are accessible only from the session that created them, and they are automatically deleted when the session that created them ends. Global temporary tables are accessible from all active sessions, but they are automatically deleted when the session that created them terminates. Do not qualify temporary table names with a schema name.

(column_name data_type attributes [,])

A list is defined that lists one or more columns, their data types, and additional attributes, such as nullability, separated by commas. Each table declaration must include at least one column, for which you can specify:

column_name

Specifies the column name. It must be an identifier that is valid according to the rules of the particular RDBMS. The name must be meaningful!

data_type

Associates a specific data type with a column named column_name. For those data types that allow you to specify their length, there is additional parameter length, for example VARCHAR(255). The data type must be valid in the RDBMS. For a complete description of valid data types and vendor-specific variations, see Chapter 2. Attributes

Associates the specified constraint attributes with the column. You can specify multiple attributes for a single column named column_name. No commas required. Typical ANSI attributes include the following.

NOT NULL

The column does not allow NULL values (or allows if the NOT NULL clause is omitted). Any INSERT and UPDATE statements that attempt to place a NULL value in a column with the NOT NULL attribute will fail and will be rolled back.

DEFAULT expression

The column will use the value of the expression if the INSERT or UPDATE statement does not supply any value. The expression must be valid for the column's data type; for example, you cannot use any alphabetic characters in a column of type INTEGER. The expression can be a string or a numeric literal, but you can also specify a user-defined or system function. The SQL 2003 standard allows the following system functions to be used in the DEFAULT clause: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D ATE, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMESTAMP, ARRAY, or ARRAY.

COLLATE collation_name

The collation used is determined, that is, the sort order in the corresponding column. The name of the mapping depends on the platform. If a collation name is not specified, the default collation is the character set used in the column. REFERENCES ARE CHECKED This parameter determines whether references in the REF column defined with the scope option will be checked. An additional ON DELETE clause determines whether the values in the records referenced by the deleted record will be set to NULL or whether the operation will be restricted.

CONSTRAINT constraint name [constraint_type [constraint]]

The parameter assigns the column a constraint and optionally a constraint name. Types of constraints are discussed in Chapter 2. Because a constraint is associated with a specific column, the constraint declaration assumes that that column will be the only one in the constraint. Once the table is created, the constraint is considered a table-level constraint.

column_name

A column is defined with special options, such as a scope option, a default value option, a column level constraint, or a COLLATE clause. In many implementations, the WITH OPTIONS clause is limited to creating typed tables.

LIKE table_name

The new table is created with the same column definitions as the existing table table_name.

REFIS column_name (SYSTEM GENERATED | USER GENERATED DERIVED]

Defines an object identifier (OID) column in typed tables. An object identifier is required for a table that is the root of a table hierarchy. According to this parameter, the REF column can be generated automatically by the system (SYSTEM GENERATED), manually specified by the user when entering a row (USER GENERATED), or created from another REF column (DERIVED). The parameter requires that the REFERENCES attribute be included in the column_name column.

CONSTRAINT constraint type [constraint name] [, …]

A table is assigned one or more constraints. This setting is noticeably different from column-level restrictions because column-level restrictions are assumed to apply only to the column to which they are associated. For table-level constraints, it is possible to associate multiple columns with a constraint. For example, in a sales table, you might need to declare unique constraint to the concatenated key store_id, order_id and order_date. This can only be done using a table level constraint. For a detailed discussion of limitations, see Chapter 2.

OF type_name [table_definition]

The table is declared to be based on a ready-made user-defined type. In this situation, the table can have only one column for each structured type attribute, plus an additional column defined in the REF IS clause. The REF data type is described in detail in the CREATE/ALTER TYPE statement section. This clause is incompatible with the LIKE table_name clause. Where:

UNDER supertable [definition/tables]

Declares an immediate supertable for the current table in the same schema (if it exists). Optionally, you can specify a full table_definition for the supertable, filling it with columns, constraints, and the like.

ON COMMIT (PRESERVE ROWS DELETE ROWS]

The ON COMMIT PRESERVE ROWS clause preserves temporary table data rows when a COMMIT statement is executed. The ON COMMIT DELETE RO WS clause deletes all rows of data in a temporary table when a COMMIT statement is executed.

ADD column_name data_type attributes

A column with the appropriate data type and attributes is added to the table.

ALTER column_name SET DEFAULT default_value

Adds a default value to the column (if it does not exist) or changes an existing value.

ALTER column_name DROP DEFAULT

The default value is completely removed from the specified column.

ALTER column_name ADD SCOPE table_name

A scope is added to the specified column. A scope is a reference to a custom data type.

ALTER column_name DROP SCOPE

The scope is removed from the specified column. The RESTRICT and CASCADE clauses are explained at the end of this list.

DROP COLUMN column_name

The specified column is removed from the table. The RESTRICT and CASCADE clauses are explained at the end of this list.

ADD table_constraint

A constraint is added to the table with specified name and characteristics.

DROP CONSTRAINT constraint name

The existing constraint is removed from the table.

RESTRICT

When this clause is specified, the RDBMS cancels the command if it finds objects in the database that depend on the object.

When this clause is specified, the RDBMS deletes all other objects that depend on this object.

ALTER TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:

ADD create_definition

or ADD (create_definition, create_definition,...)

or ADD INDEX (index_col_name,...)

or ADD PRIMARY KEY (index_col_name,...)

or ADD UNIQUE (index_col_name,...)

or ADD FULLTEXT (index_col_name,...)

or ADD FOREIGN KEY index_name (index_col_name,...)

or ALTER col_name (SET DEFAULT literal | DROP DEFAULT)

or CHANGE old_col_name create_definition

or MODIFY create_definition

or DROP col_name

or DROP PRIMARY KEY

or DROP INDEX index_name

or DISABLE KEYS

or ENABLE KEYS

or RENAME new_tbl_name

or ORDER BY col

or table_options

The ALTER TABLE statement provides the ability to change the structure of an existing table. For example, you can add or remove columns, create or destroy indexes, or rename columns or the table itself. You can also change the comment for the table and its type.

The ALTER TABLE statement creates a temporary copy of the original table at runtime. The required change is performed on the copy, then the original table is deleted and the new table is renamed. This is done so that all updates except failed ones are automatically included in the new table. During ALTER TABLE execution, the source table is readable by other clients. Update and write operations on this table are suspended until the new table is ready.

It should be noted that when using any other option for ALTER TABLE other than RENAME, MySQL will always create a temporary table, even if the data does not strictly need to be copied (for example, when a column name changes). For MyISAM tables, you can increase the speed of rebuilding the index portion (which is the slowest part of the table recovery process) by setting the myisam_sort_buffer_size variable to a large enough value.

To use the ALTER TABLE statement, you must have ALTER, INSERT, and CREATE privileges on the table.

The IGNORE option is a MySQL extension to ANSI SQL92. It controls the operation of ALTER TABLE when there are duplicate unique keys in new table. If the IGNORE option is not specified, then the process for this copy is interrupted and rolled back. If IGNORE is specified, then for rows with duplicate unique keys, only the first row is used and the rest are removed.

You can run multiple ADD, ALTER, DROP, and CHANGE statements in a single ALTER TABLE command. This is an extension of MySQL to ANSI SQL92, where only one of the expressions mentioned in a single ALTER TABLE command is allowed.

The CHANGE col_name, DROP col_name, and DROP INDEX options are also MySQL extensions to ANSI SQL92.

The MODIFY option is an Oracle extension to the ALTER TABLE command.

The optional word COLUMN represents "white noise" and may be omitted.

When using ALTER TABLE table_name RENAME TO new_name without any other options, MySQL simply renames the files corresponding to the given table. In this case, there is no need to create a temporary table. The create_definition statement for ADD and CHANGE uses the same syntax as for CREATE TABLE. Note that this syntax includes the column name, not just its type.

A column can be renamed using the CHANGE column_name create_definition statement. To do this, you must specify the old and new column names and its current type. For example, to rename the INTEGER column to b, you could do the following:

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

If you change a column's type but not its name, the CHANGE expression syntax still requires both column names to be specified, even if they are the same. For example:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

However, starting with MySQL version 3.22.16a, you can also use a MODIFY expression to change the type of a column without renaming it:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

When using CHANGE or MODIFY to reduce the length of a column that is part of an index (for example, an index on the first 10 characters of a VARCHAR column), you cannot make the column shorter than the number of characters indexed.

When you change a column's type using CHANGE or MODIFY, MySQL attempts to convert the data to the new type as correctly as possible.

In MySQL 3.22 and later, you can use FIRST or ADD ... AFTER column_name to add a column at a given position within a table row. By default, the column is added at the end. Beginning with MySQL 4.0.1, you can also use the FIRST and AFTER keywords in the CHANGE or MODIFY options.

The ALTER COLUMN option sets a new default value for a column or removes an old one. If the old default value is removed and the column can be NULL, then the new default value will be NULL. If the column cannot be NULL, then MySQL assigns a default value. The DROP INDEX option removes an index. It is an extension of MySQL to ANSI SQL92. If columns are removed from a table, those columns are also removed from any index that they are part of. If all the columns that make up an index are deleted, that index is also deleted.

If a table contains only one column, then that column cannot be deleted. Instead, you can drop this table using the DROP TABLE command.

The DROP PRIMARY KEY option removes the primary index. If such an index does not exist on a given table, then the first UNIQUE index on that table is removed. (MySQL marks the first unique key UNIQUE as the PRIMARY KEY if no other PRIMARY KEY has been explicitly specified.) When you add a UNIQUE INDEX or PRIMARY KEY to a table, it is stored before any other non-unique keys so that duplicate keys can be identified as early as possible.

The ORDER BY option allows you to create a new table with rows in a specified order. Please note that the created table will not retain this row order after insert and delete operations. In some cases, this feature can make sorting operations easier in MySQL if the table has a column arrangement that you would like to have in the future. This option is mainly useful if you know in advance a specific order in which rows will be predominantly requested. Using this option after significant table transformations allows for better performance.

When you use the ALTER TABLE command on MyISAM tables, all non-unique indexes are created in a separate batch (like REPAIR). This will make ALTER TABLE faster when you have multiple indexes.

As of MySQL 4.0, the above feature can be enabled explicitly. The ALTER TABLE ... DISABLE KEYS command blocks MySQL from updating non-unique indexes on MyISAM tables. You can then use the ALTER TABLE ... ENABLE KEYS command to recreate the missing indexes. Because MySQL does this using a special algorithm that is much faster than inserting keys one by one, locking keys can provide significant speedup on large arrays of inserts.

By using the C API mysql_info() function, you can determine how many records were copied, and also (when using IGNORE) how many records were removed due to duplicate unique key values.

The FOREIGN KEY, CHECK, and REFERENCES statements actually do nothing. They are included for compatibility reasons only, to make it easier to port code from other SQL servers and run applications that create referenced tables.

The following examples show some uses of the ALTER TABLE command. The example starts with table t1, which is created as follows:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

To rename a table from t1 to t2:

mysql> ALTER TABLE t1 RENAME t2;

To change the type of a column from INTEGER to TINYINT NOT NULL (keeping the name the same) and change the type of a column from CHAR(10) to CHAR(20) and renaming it from bnac:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

To add a new TIMESTAMP column named d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add an index to column d and make column a the primary key:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

To delete column c:

mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT numeric column named c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,

Note that the column is indexed because AUTO_INCREMENT columns must be indexed; In addition, column c is declared NOT NULL because indexed columns cannot be NULL.

When you add an AUTO_INCREMENT column, the values in that column are automatically populated with sequential numbers (as records are added). The first sequence number can be set by executing the command SET INSERT_ID=#beforeALTER TABLE or using the table option AUTO_INCREMENT = #.