从db表1中选择数据并将其插入到php中的另一个db表2中

I have two different Databases, names:

我有两个不同的数据库,名称:

dbtest: Table 1

dbtest:表1

dbtest2: Table 2

dbtest2:表2

I want to select all the data and new entries from dbtest Table 1 to dbtest2 Table 2.

我想从dbtest表1到dbtest2表2中选择所有数据和新条目。

I have tried this

我试过这个

$sqlfin = "INSERT INTO dbtest2.Table2 SELECT * FROM dbtest.Table1";
$resultfi = mysqli_query($db_conn, $sqlfin);

But no luck so far. How can I assure that new Records are insert into both table ? Any help would be appreciated?

但到目前为止没有运气。我怎样才能确保将新记录插入两个表中?任何帮助,将不胜感激?

4 个解决方案

#1

lets try it in this format

让我们尝试这种格式

INSERT INTO `dbtest2`.`Table2` SELECT * FROM `dbtest`.`Table1`

The following conditions hold for INSERT ... SELECT statements:

INSERT ... SELECT语句包含以下条件:

Specify IGNORE to ignore rows that would cause duplicate-key violations.

指定IGNORE以忽略会导致重复键冲突的行。

AUTO_INCREMENT columns work as usual.

AUTO_INCREMENT列照常工作。

To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT statements (see Section 8.11.3, “Concurrent Inserts”).

为确保二进制日志可用于重新创建原始表,MySQL不允许INSERT ... SELECT语句的并发插入(请参见第8.11.3节“并发插入”)。

To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

为了避免SELECT和INSERT引用同一个表时出现模糊的列引用问题,请为SELECT部分中使用的每个表提供唯一的别名,并使用适当的别名限定该部分中的列名。

INSERT ... SELECT Syntax

INSERT ... SELECT语法

Create Trigger: for adding new entries

创建触发器:用于添加新条目

CREATE TRIGGER copy_record BEFORE INSERT ON dbtest.Table1 
FOR EACH ROW 
BEGIN 
   INSERT INTO dbtest2.Table2 (first_name, last_name) VALUES (new.first_name, new.last_name); 
END

trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:

trigger_event指示激活触发器的操作类型。允许使用这些trigger_event值:
INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE
statements.
UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE
and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.

表上的TRUNCATE TABLE语句不会激活此触发器,因为它们不使用DELETE。删除分区也不会激活DELETE触发器。

CREATE TRIGGER Syntax

CREATE TRIGGER语法

#2

Try this query for your desired task :

尝试此查询以执行所需任务:

Query First (Create Table exactly same like in old database, if you have not):

查询优先(创建表与旧数据库完全相同,如果没有):

CREATE TABLE dbtest2.Table2 LIKE dbtest.Table1;

Query Second (Insert all data to newly created table) :

查询秒(将所有数据插入新创建的表):

INSERT INTO dbtest2.Table2 SELECT * FROM dbtest.Table1;

#3

Your query looks correct but will fail if the 2 tables have a different structure. Specify the columns to avoid it like:

您的查询看起来正确但如果2个表具有不同的结构将失败。指定列以避免它:

INSERT INTO dbtest2.Table2 (2_col_1, 2_col_2) SELECT 1_col_1, 1_col_2 FROM dbtest.Table1

With PDO (kind of alternative answer, I don't know much for Mysqli):
You could connect to Mysql with PDO without giving a database name when working with multiple ones (but this isn't mandatory), like:

$db = new PDO( "mysql:host=" . $host . ";", $user, $password, $options );

Then write the Database names, tables and columns like when making a JOIN (as you did): separated by a .

然后编写数据库名称,表格和列,就像进行JOIN一样(就像你做的那样):用a分隔。

// an example ..
$useDb = $db->query("INSERT INTO db_1.table1 (value_1, value_2) SELECT value_3, value_4 FROM db_2.table2 WHERE db_2.table2.id = 5");

(example tested and working fine)

(示例测试并正常工作)

#4

INSERT INTO dbtest2 ( 
      id, 
      name, 
      status ) 
SELECT id, 
      name,          
       '1'
FROM dbtest
ORDER BY id ASC

You can use INSERT...SELECT syntax. Note that you can quote '1' directly in the SELECT part.

您可以使用INSERT ... SELECT语法。请注意,您可以直接在SELECT部分中引用“1”。

#1