Mysql Replicated tables across databases (in the same IP address) example _mysql

Data replication classification between database tables

When using database development, the data between some tables is often imported into each other. Of course, you can write program implementation, but the program often needs to develop the environment, inconvenient. The most convenient is to import directly using SQL language. It is convenient and easy to modify. Here's how to import it.

1, table structure of the same table, and in the same database (e.g., table1,table2)

Sql:

Copy Code code as follows:

INSERT INTO table1 select * from table2 (full copy)
INSERT INTO TABLE1 SELECT DISTINCT * from table2 (do not replicate duplicate records)
Insert INTO table1 select Top 5 * table2 (first five records)

2, not in the same database (e.g., DB1 table1,db2 table2)

Sql:
[Code]
INSERT INTO Db1.table1 select * from Db2.table2 (full copy)
INSERT INTO DB1.TABLE1 SELECT DISTINCT * from Db2table2 (do not replicate duplicate records)
Insert INTO TDB1.ABLE1 select Top 5 * DB2TABLE2 (first five records)

3. Table structure different table or copy part record (for example, Dn_user,dn_user2)

A. Build a new table [Dn_usertemp] (Add a column to the cousin Dn_user)

Copy Code code as follows:

CREATE TABLE [Dn_usertemp] ([Num] [numeric] (0) IDENTITY (1, 1) not NULL)
[Id] [idtype] not NULL,
[Name] [Fntype] Not NULL,
[Descript] [Dstype] Null
[LOGONNM] [Idtype] Not NULL,
[Password] [Idtype] Null
[Gender] [Char] (1) NULL,
[Quited] [Booltype] Not NULL,
[Offduty] [Booltype] Not NULL,
[Stopped] [Booltype] Not NULL,
[Osbind] [Booltype] Not NULL,
[Domain] [Idtype] Null
[EMail] [Fntype] Null
[Unitid] [Idtype] Null
[Branchid] [Idtype] Null
[Dutyid] [Idtype] Null
[Levelid] [Idtype] Null
[ClassId] [Idtype] Null
[typeID] [Idtype] Null
[IP] [varchar] (a) COLLATE chinese_prc_ci_as NULL,
[Expiredt] [DateTime] Null
[Sort] [INT] Not NULL,
[Allowdel] [Booltype] Not NULL,
[Unitchief] [Booltype] Not NULL,
[Branchchief] [Booltype] Not NULL,
[Unitdeputy] [Booltype] Not NULL,
[Branchdeputy] [Booltype] Not NULL,

[Num] [Numeric] (18, 0) IDENTITY (1, 1) not NULL

) on [PRIMARY]

B. Copying dn_uer2 data into dn_usertemp

Sql:insert into dn_usertemp select * from Dn_user2

C. copy dn_usertemp into Dn_user

Sql:

Copy Code code as follows:

DECLARE @i int
DECLARE @j int
DECLARE @Name Fntype
Set @i=1
Select @j=count (*) from dn_usertemp
While @i<@j 1
Begin

Select @Name =name from Dn_usertemp where num=@i
Print @Name
Insert into Dn_user (Name) VALUES (@Name) where num=@i
Select @i=@i 1
End

MySQL Database copy table data

The MYTBL table in the production database is quickly copied to the mytbl_new,2 command as follows:

Copy Code code as follows:

CREATE TABLE mytbl_new like production.mytbl;
INSERT mytbl_new SELECT * from PRODUCTION.MYTBL;

The first command is to create a new datasheet mytbl_new and copy the MYTBL datasheet structure.

The second command is to copy the data from the datasheet mytbl to the new table mytbl_new.

Note: PRODUCTION.MYTBL is the name of the database that specifies the table to be replicated production. It is optional.

If there is no production. , the MySQL database will assume mytbl in the current operation of the database.

In addition: Copy the data in the MySQL database as:

Copy Code code as follows:

SELECT * Into destable from SourceTable is supported in MSSQL and is not supported in MySQL
INSERT INTO destable select * from SourceTable