MySQL将8000多个行从一个表转移到另一个PHP [英] mySQL transfer 8000+ rows from one table to another PHP

问题描述

我正在处理一个脚本，在该脚本中，我希望将8000+行从一个表转移到具有选定列的另一个数据库中的另一个表.到目前为止，我已经考虑过使用PDOStatement::fetchAll()作为$result数组来获取整个源表的选定列，然后使用PDO::prepare()方法作为

I am working on a script in which I want to transfer 8000+ rows from one table to another table in another database with selected columns. By far I have thought to fetch the selected columns of the entire source table using PDOStatement::fetchAll() as a $result array and then prepare a mySQL insert statement using PDO::prepare() method as

INSERT INTO targetTable (targetColumn1,targetColumn2...) VALUES 
(sourceRow1ForColumn1,sourceRow1ForColumn2,..),
(sourceRow2ForColumn1,sourceRow2ForColumn2,..),
(sourceRow3ForColumn1,sourceRow3ForColumn2,...),...

但是我认为这不是一种有效的方法，应该有一种最佳方法来完成我想做的事情.有什么更好的方法可以做我想做的事?

But I think that this is not an efficient way and there should be an optimal way to do what I want to do. Is there any better way I can do what I want to do?

推荐答案

执行此操作的最佳方法是使用INSERT ... SELECT语句.

The optimal way to do this would be to use an INSERT ... SELECT statement.

INSERT INTO targetTable (targetColumn1,targetColumn2)
SELECT expr1, expr2 FROM one_table

expr1和expr2表示分别返回要插入到targetColumn1和targetColumn2列中的值的表达式.

expr1 and expr2 represent expressions that return the values that you want to insert into targetColumn1 and targetColumn2 columns, respectively.

这些表达式可以简单地引用源表中的列名称.

The expressions could be as simple reference to a colum name in the source table.

如果表位于同一MySQL实例上的不同数据库(方案)中，则可以使用数据库名称来限定表名. (不合格的表名是指当前数据库中的表(即USE mydatabase.)

If the tables are in different databases (schemas) on the same MySQL instance, you can qualify the table name with the name of the database. (An unqualified table name refers to a table in the current database (i.e. USE mydatabase.)

如果当前数据库是目标数据库，请在FROM子句中限定名称表

If your current database is the target database, qualify the name table in the FROM clause

INSERT INTO targetTable (targetColumn1,targetColumn2)
SELECT expr1, expr2 FROM anotherdb.one_table
                         ^^^^^^^^^^

如果当前数据库包含源表，请限定targetTable的名称.

If the current database contains the source table, qualify the name of the targetTable.

或者只限定两个表名，并且哪个数据库是最新的都没关系.

Or just qualify both table names, and it doesn't matter what database is current.

只需以对每个表具有足够特权的用户身份连接到MySQL.

Just connect to MySQL as a user that has sufficient privileges on each table.

GRANT SELECT ON anotherdb.one_table TO myuser@'%';
GRANT INSERT ON targetdb.targetTable TO myuser@'%';

---

如果插入尝试插入违反targetTable中唯一约束的行，则INSERT ... SELECT和INSERT ... VALUES语句都将发生相同的情况.该语句将失败.

---

If the insert attempts to insert a row that violates a unique constraint in the targetTable, the same thing would happen with an INSERT ... SELECT as would with the INSERT ... VALUES statement... the statement will fail.

如果您想忽略有关重复项的错误，而只是跳过这些行，请使用INSERT IGNORE ... SELECT属性. (IGNORE关键字使MySQL不会跳过该行，而不会在违反唯一键约束时抛出错误.

If you want to disregard errors about duplicates, and just skip over those rows, then use an INSERT IGNORE ... SELECT atatement. (The IGNORE keywords causes MySQL not to skip over the row, rather than throwing an error when a unique key constraint is violated.

或者，您可以编写SELECT语句以排除目标表中已经存在的行.

Or, you could write the SELECT statement to exclude rows that already exist in the target table.

这篇关于MySQL将8000多个行从一个表转移到另一个PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！