MySQL replication table-select into from

Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against coronavirus.

 

Base table:

mysql> select * from staff;
+----+----------+-------+
| id | name     | slary |
+----+----------+-------+
|  3 | haofugui | 10000 |
|  4 | guoming  |  3500 |
|  5 | haotian  |  2900 |
+----+----------+-------+
3 rows in set (0.00 sec)

mysql> describe staff;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| slary | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Statement 1:select [field1, Field2 | *] into Table2 (in externaldatabase) from Table1 where condition;

Statement 2:select a.field1,b.field2 into C (in externaldatabase) from A [Inner | left | right] join B on [conditi On1] where condition2;

Description: [in Externaldatabase] enables data replication across databases.

Note: The target table (that is, Table2 or C) does not exist, and MySQL is automatically created, which is a very different point from the INSERT into select.

Example:

Mysql> SELECT * into Persons_backup from Persons;

Mysql> SELECT * / / data replication across the database
     -> INTO Persons IN ‘Backup.mdb‘
     -> FROM Persons;
mysql> SELECT LastName,FirstName
    -> INTO Persons_backup
    -> FROM Persons;

mysql> SELECT LastName,Firstname
    -> INTO Persons_backup
    -> FROM Persons
    -> WHERE City=‘Beijing‘;

Mysql> SELECT Persons.LastName, Orders.OrderNo / / table join replication, that is, multi-table replication
     -> INTO Persons_Order_Backup
     -> FROM Persons
     -> INNER JOIN Orders
     -> ON Persons.Id_P=Orders.Id_P;

MySQL replication table-select into from