MySQL replication table structure, table data methods

from:http://blog.163.com/[email protected]/blog/static/109463675201191173221759/

MySQL add column, modify column, delete column's SQL statement notation

ALTER table: Add, modify, delete table columns, constraints, and other table definitions.

• View column: Desc table name;
• Modify table name: ALTER TABLE T_book Rename to BBB;
• Add column: ALTER TABLE name add column name varchar (30);
• Delete column: ALTER TABLE table name drop column name;
• Modify column name: ALTER TABLE BBB change nnnnn hh int;
• Modify Column properties: ALTER TABLE T_book modify name varchar (22);

One, duplicate table structure
Method 1:
Mysql> CREATE table a like users; Duplicate table structure
Query OK, 0 rows affected (0.50 sec)
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| A |
| Users |
+----------------+
2 rows in Set (0.00 sec)
Mysql> CREATE table a like users; Duplicate table structure
Query OK, 0 rows affected (0.50 sec)
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| A |
| Users |
+----------------+
2 rows in Set (0.00 sec)

Method 2:
mysql> CREATE TABLE B select * from users limit 0; Duplicate table structure
Query OK, 0 rows Affected (0.00 sec)
records:0 duplicates:0 warnings:0
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| A |
| B |
| Users |
+----------------+
3 Rows in Set (0.00 sec)
mysql> CREATE TABLE B select * from users limit 0; Duplicate table structure
Query OK, 0 rows Affected (0.00 sec)
records:0 duplicates:0 warnings:0
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| A |
| B |
| Users |
+----------------+
3 Rows in Set (0.00 sec)

Method 3:
Mysql> Show CREATE TABLE users\g; Display the SQL of the Genesis table here you can also use the Desc users; Show the structure of the table
1. Row ***************************
Table:users
Create table:create table ' users ' (//change name
' ID ' bigint (unsigned) not NULL auto_increment,
' user_name ' varchar not NULL DEFAULT ' ',
' User_pass ' varchar (+) not NULL DEFAULT ' ',
PRIMARY KEY (' ID ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8//Change auto_increment
1 row in Set (0.00 sec)
Mysql> Show CREATE TABLE users\g; Displaying the SQL for the creation of a table
1. Row ***************************
Table:users
Create table:create table ' users ' (//change name
' ID ' bigint (unsigned) not NULL auto_increment,
' user_name ' varchar not NULL DEFAULT ' ',
' User_pass ' varchar (+) not NULL DEFAULT ' ',
PRIMARY KEY (' ID ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8//Change auto_increment
1 row in Set (0.00 sec) Copy the SQL statement, change the table name and Atuo_increment, and then execute it.
Second, copy table data, and table structure
Method 1:
mysql> CREATE TABLE C select * from users; SQL for copying tables
Query OK, 4 rows Affected (0.00 sec)
Records:4 duplicates:0 warnings:0
mysql> CREATE TABLE C select * from users; SQL for copying tables
Query OK, 4 rows Affected (0.00 sec)
Records:4 duplicates:0 warnings:0

Method 2:
mysql> CREATE TABLE D select User_name,user_pass from users where id=1;
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0
mysql> CREATE TABLE D select User_name,user_pass from users where id=1;
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0 above 2 kinds of methods, convenient, fast, strong flexibility.
Method 3:
First create an empty table, INSERT into new table SELECT * from old table, or
INSERT into new Table (Field 1, Field 2,.......) SELECT field 1, Field 2,...... From old table
This method is not very convenient, also I used to use often.

MySQL replication table structure, table data methods