Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to copy tables in MySQL". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Foreign keys in 1.MySQL

1) can only be used for InnoDB tables.

2) the parent table can be in another database.

3) as long as the referenced field in the parent table has an index, there can be duplicate values.

4) add on delete cascade after "foreign key (colname) references tabname (colname)" to achieve cascading deletion.

Note: in addition to CASCADE,delete, keywords such as SET NULL,RESTRICT,NO ACTION can also be followed. In addition, cascading updates can be implemented using the on update clause, and both can be used at the same time, which will not be repeated here.

[@ more@]

two。 Table type

MyISAM and InnoDB are the most important table types in MySQL. A MyISAM table has three corresponding files (tabname.frm, tabname.MYD, tabname.MYI) in the file system, while an InnoDB table has only one corresponding file (tabname.frm).

1) MyISAM

MyISAM extends the basic ISAM type, which is portable between different operating systems and platforms, supports large table files (larger than 4GB), allows indexing of BLOB and TEXT columns, and compression of tables and table indexes (this feature is useful for BLOB and TEXT fields). It can also constrain the length of the VARCHAR field, or automatically adjust dynamically according to the data, supporting the use of key prefixes and the use of complete key search records.

When MySQL starts, it automatically checks the MyISAM table to prevent crashes and can even fix errors when they occur. Table data files and table index files can be stored in different locations, or even in different file systems. Even for tables that perform a large number of insert, update, and delete operations, intelligent anti-fragmentation logic ensures high-performance collaboration.

2) InnoDB

InnoDB is the default table type in MySQL 5.1. it is fully compatible with ACID, has comparable performance with MyISAM, fully supports MySQL transaction processing without slowing down speed or performance, and is portable between different operating systems and architectures. InnoDB provides row-level and table-level locking, as well as unlocked read operations (similar to Oracle) and multiple versions. In addition, it provides support for foreign key, submit, review, and roll forward operations.

InnoDB supports the automatic creation of hash indexes in memory to improve performance when needed, as well as the use of buffering to improve reliability and speed of database operations. Asynchronous input / output and a series of read buffers improve the speed of data retrieval. "partner algorithm" and Oracle type tablespaces can optimize files and manage memory.

In addition, there are ISAM (mainly compatible with older versions), HEAP (temporary tables created in memory), BerkleyDB, MERGE (multiple MyISAM tables combined into a single table), and so on.

3. Other table modifiers

In addition to TYPE, there are many other properties that can be specified to control the creation of the table.

Attribute description

The value that AUTO_INCREMENT inserted into the AUTO_INCREMENT field for the first time

Whether CHECKSUM stores table checksums (Boolean values)

Descriptive comments on the COMMENT table

Maximum number of rows stored in the MAX_ROWS table

Minimum number of rows stored in the MIN_ROWS table

Whether PACK_KEYS compresses the table index (Boolean)

A table that maps UNION to a separate MERGE table

Location of the DATA DIRECTORY data file

Location of the INDEX DIRECTORY index file

4. Copy tabl

In MySQL, you can combine CREATE TABLE and SELECT statements to copy tables. For example:

Mysql > create table test_crttb

-> (

-> id tinyint (3)

Name varchar (15)

-> primary key (id)

->)

Query OK, 0 rows affected (0.10 sec)

Mysql > insert into test_crttb values (1, 'Adam')

Query OK, 1 row affected (0.04 sec)

Mysql > insert into test_crttb values (2, 'Bob')

Query OK, 1 row affected (0.03 sec)

Mysql > insert into test_crttb values (3, 'Clark')

Query OK, 1 row affected (0.03 sec)

Mysql > select * from test_crttb

+-+ +

| | id | name |

+-+ +

| | 1 | Adam |

| | 2 | Bob |

| | 3 | Clark |

+-+ +

3 rows in set (0.00 sec)

Mysql > create table test_crttb2 select * from test_crttb

Query OK, 3 rows affected (0.52sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from test_crttb

+-+ +

| | id | name |

+-+ +

| | 1 | Adam |

| | 2 | Bob |

| | 3 | Clark |

+-+ +

3 rows in set (0.00 sec)

Note that select is followed by "create table." Unlike Oracle, there is no as in the back and front.

You can add an invalid where condition to create an empty table with the same structure:

Mysql > desc test_crttb

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (3) | NO | PRI | 0 | |

| | name | varchar (15) | YES | | NULL |

+-+ +

2 rows in set (0.02 sec)

Mysql > desc test_crttb3

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (3) | NO | | 0 | |

| | name | varchar (15) | YES | | NULL |

+-+ +

2 rows in set (0.03 sec)

However, this method can only copy the table itself, and the keys on the table need to be created manually. To copy the keys on the table while copying the table, you can use the following method:

Mysql > create table test_crttb4 like test_crttb

Query OK, 0 rows affected (0.13 sec)

Mysql > show create table test_crttb

+-

-+

| | Table | Create Table |

| |

+-

-+

| | test_crttb | CREATE TABLE `test_ crttb` (

`id`tinyint (3) NOT NULL DEFAULT'0'

`name` varchar (15) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-

-+

1 row in set (0.00 sec)

Mysql > show create table test_crttb4

+-

-+

| | Table | Create Table |

| |

+-

-+

| | test_crttb4 | CREATE TABLE `test_ crttb4` (

`id`tinyint (3) NOT NULL DEFAULT'0'

`name` varchar (15) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-

-+

1 row in set (0.00 sec)

In this way, an empty table with the same structure is created with the keys on the original table. At this point, you can insert the record from the original table:

Mysql > insert into test_crttb4 select * from test_crttb

Query OK, 3 rows affected (0.24 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from test_crttb4

+-+ +

| | id | name |

+-+ +

| | 1 | Adam |

| | 2 | Bob |

| | 3 | Clark |

+-+ +

3 rows in set (0.00 sec)

You can also create a "mixed table" that contains both columns from the original table and new columns:

Mysql > create table test_crttb5

-> (

Tel smallint (15)

->)

-> select * from test_crttb

Query OK, 3 rows affected (0.16 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from test_crttb5

+-+-+

| | tel | id | name | |

+-+-+

| | NULL | 1 | Adam |

| | NULL | 2 | Bob |

| | NULL | 3 | Clark |

+-+-+

3 rows in set (0.00 sec)

Use the first and after clauses of the alter table statement to reposition the fields:

Mysql > alter table test_crttb5 modify id tinyint (3) first

Query OK, 3 rows affected (0.65 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > desc test_crttb5

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (3) | YES | | NULL |

| | tel | smallint (15) | YES | | NULL |

| | name | varchar (15) | YES | | NULL |

+-+ +

3 rows in set (0.01sec)

Mysql > alter table test_crttb5 modify tel smallint (15) after name

Query OK, 3 rows affected (.56 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > desc test_crttb5

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (3) | YES | | NULL |

| | name | varchar (15) | YES | | NULL |

| | tel | smallint (15) | YES | | NULL |

+-+ +

3 rows in set (0.01sec)

This is the end of the content of "how to copy tables from MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.