Basic usage of ALTER TABLE command in MYSQL and speed-increasing optimization _mysql

First, basic usage

1. Add Column

ALTER TABLE Tbl_name add col_name type

For example, add a column of weight to the pet table,

Mysql>alter table Pet Add weight int;

2. Delete Column

ALTER TABLE Tbl_name drop col_name

For example, delete the weight column in the pet table

Mysql>alter table Pet Drop weight;

3. Change the column

To change the properties of the column and change the name of the column

To change the properties of a column--Method 1:

ALTER TABLE tbl_name Modify Col_name type

For example, changing the type of weight

Mysql>alter Table Pet Modify weight varchar (30);

To change the properties of a column--Method 2:

ALTER TABLE tbl_name change Old_col_name col_name type

For example, changing the type of weight

ALTER TABLE PET Change weight weight varchar (30);

Change the name of the column:

ALTER TABLE tbl_name Change Old_col_name col_name

For example, change the name of weight in the pet table:

Mysql>alter Table Pet Change weight wei;

4. Change the name of the table

ALTER TABLE Tbl_name Rename NEW_TBL

For example, rename the pet table to animal

Mysql>alter table Pet Rename animal;

Second, the optimization of ALTER TABLE
in the day-to-day maintenance of the system, it is often necessary to update the table structure, such as adding/removing a field, changing the length of a varchar field, and so on. MySQL's approach to modifying the table structure is to create a new structure table first, then insert the contents of the old table into the new table by executing the INSERT statement, and then delete the entire old table. This is not a problem when the volume of data is small, but it may take a lot of time to process when the data is large.

The operation of an update table structure took several hours, which was intolerable. If you're using a version prior to 5.1, you'll also have to perform a table-structured update where the database is often stopped, but fortunately the problem has improved in the latest version

If you take the appropriate approach when making a table structure update, not all of the update operations will take you a long time.
For example, you would like to update the user table with the default password of "666666", which is usually done by

mysql> ALTER TABLE user
      -> MODIFY COLUMN pwd VARCHAR not NULL DEFAULT ' 666666 ';

By show status you can see that a lot of inserts have been made during this operation, and when the number of users is large, such as millions, the thousands data will inevitably consume a lot of time.

But if you use the way below to update, the time will be greatly shortened

mysql> alter TABLE user
     -> alter COLUMN pwd varchar NOT null SetDefault 5;

&NBSP
Performing a show STATUS operation found that a large number of insertions did not exist and that time was greatly shortened (flush STATUS is required)
  is likely to shorten because
(1) The default value for a table field is the frm (. frm: Table structure file  . MYD: Table data file  . Myi: Table Index) file
(2) ALTER column updates the frm file without referring to the contents of the table
(3) MODIFY column involves the contents of the table Data
 
  From the previous case can be seen if the operation of the process involves only frm file changes, the table structure of the update efficiency will be greatly improved, but many times when there is no need for MySQL will also be the reconstruction of the table. If you are willing to take risks, you can modify the frm file to speed up the purpose of modifying the table
 
Not all table structure changes can improve the efficiency of the modification by modifying the frm file. Some of the following changes can be made to update by modifying the frm file:
(1)       changing the default value for a field
(2)        Add/Remove the Auto_increment property of the field
(3)       Add/remove/Modify the constant value of the enum. For a delete operation, if a field references this constant value, the structure of the query after deletion is an empty string
 
to increase the efficiency of modifying the table structure by using ALTER column and modifying the frm file, for example, by updating the default Value property of the field
&NBSP
1 execute alter COLUMN
1.1   First prepare a dictionary table

CreateTable IF not EXISTS dictionary (  
  ID Int (a) unsigned not nullauto_increment,
  Word varchar (m) Not null,
   
    mean varchar (+) not NULL,
   PRIMARY KEY (' id ')
   );  

   

1.2 Inserting some test data

Mysql>delimiter $$
mysql>drop PROCEDURE IF EXISTS sampleproc$$
Query OK, 0rows affected, 1 warning (0.01 sec )
Createprocedure Sampleproc ()
BEGIN
DECLARE Xint;
SET x = 1;
Whilex <= 110000 do
Insert Intodictionary (Word, mean) VALUES (concat (' A ', x), concat (' A means ', x));
SET x = x + 1;
End while;
End
Mysql> DELIMITER;
Mysql>call Sampleproc ();

1.3 Show STATUS observation results modify column and alter column differences
First Use Modify COLUMN

mysql> flush status;
Query OK, 0 rows Affected (0.00 sec)
mysql> ALTER TABLE dictionary
      ->modify column mean varchar () NOT NULL Default ' DEFAULT1 ';
Query OK, 110002 rows Affected (3.07 sec)
records:110002 duplicates:0 warnings:0 mysql> show
STATUS WHERE Va Riable_name like ' handler% '
  ->or variable_name like ' created% ';
+----------------------------+--------+
| Variable_name       | Value |
+----------------------------+--------+
| Handler_read_rnd_next   | 110003 |
| Handler_rollback      | 0   |
| Handler_savepoint     | 0   |
| Handler_savepoint_rollback | 0   |
| Handler_update       | 0   |
| Handler_write       | 110002
| +----------------------------+--------+

When using Alter COLUMN

 mysql> flush status;
mysql> ALTER TABLE dictionary
     -> ALTER column mean set default ' DEFAULT2 ';
Query OK, 0 rowsaffected (0.03 sec)
records:0 duplicates:0 warnings:0 mysql> show
 statuswhere variable_name Like ' handler% '
     -> or variable_name like ' created% ';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| Handler_read_rnd_next   | 0   |
| Handler_savepoint_rollback | 0   |
| Handler_update       | 0   |
| Handler_write       | 0   |

2 modifying frm files
the steps to improve the efficiency of the table structure by modifying the frm file are probably as follows
1. Backing up the relevant database files
2. Create a table structure that is exactly the same as the old one

Mysql>create table dictionary_new like dictionary;

3. Execute flush TABLES with READ LOCK. All the tables are closed.

mysql> ALTER TABLE dictionary_new
     -> Modify column mean varchar (a) default ' defaulr# ';
Mysql> Flush table with read lock;

5. Duplicate the dictionary_new.frm file as Dictionary.frm
6. Execute Unlock TABLES

       mysql> unlock tables;
       mysql> insert INTO dictionary (Word) VALUES (' Random ');
       Mysql> SELECT * from Dictionarywhere word= ' Random ';

As can be seen from the following results, the default value has been changed and does not involve changes in content

+--------+--------+----------+
| ID   | word | mean   |
+--------+--------+----------+
| 110004 | Random | defaulr# |
+--------+--------+----------+

 
7.        Drop dictionary_new