获取 MySQL 中现有行的插入语句

Get Insert Statement for existing row in MySQL

提问by Kibbee

Using MySQL I can run the query:

使用 MySQL 我可以运行查询：

SHOW CREATE TABLE MyTable;

And it will return the create table statement for the specificed table. This is useful if you have a table already created, and want to create the same table on another database.

它将返回指定表的创建表语句。如果您已经创建了一个表，并且想要在另一个数据库上创建相同的表，这将非常有用。

Is it possible to get the insert statement for an already existing row, or set of rows? Some tables have many columns, and it would be nice for me to be able to get an insert statement to transfer rows over to another database without having to write out the insert statement, or without exporting the data to CSV and then importing the same data into the other database.

是否可以为已经存在的行或一组行获取插入语句？有些表有很多列，如果我能够得到一个插入语句来将行传输到另一个数据库而不必写出插入语句，或者不必将数据导出到 CSV 然后导入相同的数据，那对我来说会很好进入另一个数据库。

Just to clarify, what I want is something that would work as follows:

只是为了澄清，我想要的是可以如下工作的东西：

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

And have the following returned for me:

并为我返回以下内容：

INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (10,'hello world','some value','2010-10-20');

回答by Kaivosukeltaja

There doesn't seem to be a way to get the INSERTstatements from the MySQL console, but you can get them using mysqldumplike Rob suggested. Specify -tto omit table creation.

似乎没有办法INSERT从 MySQL 控制台获取语句，但是您可以像 Rob 建议的那样使用mysqldump获取它们。指定-t省略表创建。

mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10"

回答by Zoltán

In MySQL Workbench you can export the results of any single-table query as a list of INSERTstatements. Just run the query, and then:

在 MySQL Workbench 中，您可以将任何单表查询的结果导出为INSERT语句列表。只需运行查询，然后：

1. click on the floppy disk near Export/Importabove the results
2. give the target file a name
3. at the bottom of the window, for Formatselect SQL INSERT statements
4. click Save
5. click Export

1. 单击Export/Import结果上方附近的软盘
2. 给目标文件一个名字
3. 在窗口底部，用于Format选择SQL INSERT statements
4. 点击 Save
5. 点击 Export

回答by Rob Prouse

Since you copied the table with the SQL produced by SHOW CREATE TABLE MyTable, you could just do the following to load the data into the new table.

由于您使用SHOW CREATE TABLE MyTable生成的 SQL 复制了表，因此您只需执行以下操作即可将数据加载到新表中。

INSERT INTO dest_db.dest_table SELECT * FROM source_db.source_table;

If you really want the INSERT statements, then the only way that I know of is to use mysqldumphttp://dev.mysql.com/doc/refman/5.1/en/mysqldump.htm. You can give it options to just dump data for a specific table and even limit rows.

如果您真的想要 INSERT 语句，那么我所知道的唯一方法是使用mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.htm。您可以为其提供选项以转储特定表的数据，甚至限制行。

回答by Chris Adams

I wrote a php function that will do this. I needed to make an insert statement in case a record needs to be replaced after deletion for a history table:

我写了一个 php 函数来做到这一点。我需要做一个插入语句，以防在删除历史表后需要替换记录：

function makeRecoverySQL($table, $id)
{
    // get the record          
    $selectSQL = "SELECT * FROM `" . $table . "` WHERE `id` = " . $id . ';';

    $result = mysql_query($selectSQL, $YourDbHandle);
    $row = mysql_fetch_assoc($result); 

    $insertSQL = "INSERT INTO `" . $table . "` SET ";
    foreach ($row as $field => $value) {
        $insertSQL .= " `" . $field . "` = '" . $value . "', ";
    }
    $insertSQL = trim($insertSQL, ", ");

    return $insertSQL;
}

回答by Chinoto Vokro

Laptop Lift's code works fine, but there were a few things I figured people may like.

Laptop Lift 的代码运行良好，但我认为人们可能会喜欢一些东西。

Database handler is an argument, not hardcoded. Used the new mysql api. Replaced $id with an optional $where argument for flexibility. Used real_escape_string in case anyone has ever tried to do sql injection and to avoid simple breakages involving quotes. Used the INSERT table (field...) VALUES (value...)...syntax so that the fields are defined only once and then just list off the values of each row (implode is awesome). Because Nigel Johnson pointed it out, I added NULLhandling.

数据库处理程序是一个参数，而不是硬编码。使用了新的 mysql api。将 $id 替换为可选的 $where 参数以提高灵活性。使用 real_escape_string 以防有人曾尝试进行 sql 注入并避免涉及引号的简单损坏。使用INSERT table (field...) VALUES (value...)...语法使字段只定义一次，然后只列出每一行的值（内爆很棒）。因为 Nigel Johnson 指出了这一点，所以我添加了NULL处理。

I used $array[$key]because I was worried it might somehow change, but unless something is horribly wrong, it shouldn't anyway.

我使用$array[$key]是因为我担心它可能会以某种方式改变，但除非出现严重错误，否则无论如何都不应该。

<?php
function show_inserts($mysqli,$table, $where=null) {
    $sql="SELECT * FROM `{$table}`".(is_null($where) ? "" : " WHERE ".$where).";";
    $result=$mysqli->query($sql);

    $fields=array();
    foreach ($result->fetch_fields() as $key=>$value) {
        $fields[$key]="`{$value->name}`";
    }

    $values=array();
    while ($row=$result->fetch_row()) {
        $temp=array();
        foreach ($row as $key=>$value) {
            $temp[$key]=($value===null ? 'NULL' : "'".$mysqli->real_escape_string($value)."'");
        }
        $values[]="(".implode(",",$temp).")";
    }
    $num=$result->num_rows;
    return "INSERT `{$table}` (".implode(",",$fields).") VALUES \n".implode(",\n",$values).";";
}
?>

回答by Henrik

I use the program SQLYOGwhere I can make a select query, point atthe results and choose export as sql. This gives me the insert statements.

我使用程序SQLYOG，我可以在其中进行选择查询，指向结果并选择导出为 sql。这给了我插入语句。

回答by Kevin Bowersox

Within MySQL work bench perform the following:

在 MySQL 工作台中执行以下操作：

1. Click Server > Data Export

2. In the Object Selection Tab select the desired schema.

3. Next, select the desired tables using the list box to the right of the schema.

4. Select a file location to export the script.

5. Click Finish.

6. Navigate to the newly created file and copy the insert statements.

1. 单击服务器 > 数据导出

2. 在对象选择选项卡中选择所需的架构。

3. 接下来，使用模式右侧的列表框选择所需的表。

4. 选择文件位置以导出脚本。

5. 单击完成。

6. 导航到新创建的文件并复制插入语句。

回答by slava157

If you want get "insert statement" for your table you can try the following code.

如果您想为您的表获取“插入语句”，您可以尝试以下代码。

SELECT 
    CONCAT(
        GROUP_CONCAT(
            CONCAT(
                'INSERT INTO `your_table` (`field_1`, `field_2`, `...`, `field_n`) VALUES ("',
                `field_1`,
                '", "',
                `field_2`,
                '", "',
                `...`,
                '", "',
                `field_n`,
                '")'
            ) SEPARATOR ';\n'
        ), ';'
    ) as `QUERY`
FROM `your_table`;

As a result, you will have insers statement:

结果，您将有insers语句：

INSERT INTO your_table(field_1, field_2, ..., field_n) VALUES (value_11, value_12, ... , value_1n);

INSERT INTO your_table( field_1, field_2, ..., field_n) VALUES (value_11, value_12, ... , value_1n);

INSERT INTO your_table(field_1, field_2, ..., field_n) VALUES (value_21, value_22, ... , value_2n);

INSERT INTO your_table( field_1, field_2, ..., field_n) VALUES (value_21, value_22, ... , value_2n);

/...................................................../

/................................................................ ..../

INSERT INTO your_table(field_1, field_2, ..., field_n) VALUES (value_m1, value_m2, ... , value_mn);

INSERT INTO your_table( field_1, field_2, ..., field_n) VALUES (value_m1, value_m2, ... , value_mn);

, where m - number of records in your_table

, 其中 m - your_table 中的记录数

回答by flash

you can use Sequel pro to do this, there is an option to 'get as insert statement' for the results obtained

您可以使用 Sequel pro 来执行此操作，对于获得的结果，可以选择“作为插入语句获取”

回答by Mosab B. Nazli

In PHPMyAdmin you can:

在 PHPMyAdmin 中，您可以：

1. click copy on the row you want to know its insert statements SQL:

1. 单击要知道其插入语句 SQL 的行上的复制：

1. click Preview SQL:

2. 单击预览 SQL：

1. you will get the created insert statement that generates it

3. 您将获得生成它的已创建插入语句

You can apply that on many rows at once if you select them and click copy from the bottom of the table and then Preview SQl

如果您选择它们​​并单击表底部的复制，然后单击预览 SQl，则可以一次将其应用于多行