在两个不同的mysql数据库中的两个表之间复制大量行

I've got a database, let's call it Database A, and another one called Database B in MySQL.

我有一个数据库,让我们称它为数据库A,另一个称为MySQL中的数据库B.

I have an SQL statement which picks up data from Database A (approximately 1 million rows) and need to insert them into Database B.

我有一个SQL语句从数据库A中获取数据(大约100万行),需要将它们插入到数据库B中。

To read the data from Database A, I am using a MySqlDataReader which contains the 1 million rows. I would now like to write them to Database B.

要从数据库A读取数据,我使用的MySqlDataReader包含100万行。我现在想把它们写到数据库B.

What I've tried doing is iterate through each row in the data reader, and write to the database with a simple INSERT stored procedure. This is taking too long however.

我尝试做的是遍历数据读取器中的每一行,并使用简单的INSERT存储过程写入数据库。但这花了太长时间。

I looked into the sql bulk data copy with C#, however I would not like to use external files.

我用C#查看了sql批量数据副本,但是我不想使用外部文件。

What options do I have?

我有什么选择?

1 个解决方案

#1

A simple query like

一个简单的查询就像

 INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2

This is executed all directly by the MySql engine avoiding any passage of data from MySql and your code

这是由MySql引擎直接执行的,避免从MySql和您的代码中传输任何数据

string cmdText = "INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2";
using(MySqlConnection cnn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    cnn.Open();
    int rows = cmd.ExecuteNonQuery();        
    Console.WriteLine(rows);
}

In a more complex scenario (for example, you need to repeat the operation and the target table initially doesn't exists) you could write a query like this

在更复杂的场景中(例如,您需要重复操作并且目标表最初不存在)您可以编写这样的查询

string cmdText = @"DROP TABLE IF EXISTS mysqldb1.table1;
                   CREATE TABLE mysqldb1.table1 LIKE mysqldb2.table2;
                   INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2";
using(MySqlConnection cnn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    ......
}

#1