获取 MySQL 中现有行的 Insert 语句

使用 MySQL 我可以运行查询:

SHOW CREATE TABLE MyTable;

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

是否有可能为已经存在的行或行集获取插入语句?有些表有很多列,如果能够获得一个 insert 语句将行传输到另一个数据库,而不必写出 insert 语句,或者不需要将数据导出到 CSV,然后将相同的数据导入到另一个数据库,那就太好了。

澄清一下,我想要的是这样的效果:

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

把下面的东西还给我:

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

由于您使用 显示创建表 MyTable生成的 SQL 复制了该表,所以只需执行下列操作将数据加载到新表中即可。

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

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

我使用程序 SQLYOG进行选择查询,指向 < img src = “ https://i.stack.imgur.com/h6nNE.png”alt = “ screen shot”> 结果并选择 export 作为 sql。这给了我插入语句。

根据您的评论,您的目标是将数据库更改从开发环境迁移到生产环境。

最好的方法是将数据库更改保存在源代码中,然后在源代码控制系统(如 git 或 svn)中跟踪它们。

你可以用这样的东西快速启动和运行: https://github.com/davejkiger/mysql-php-migrations

作为 PHP 中一个非常基本的自定义解决方案,您可以使用如下函数:

function store_once($table, $unique_fields, $other_fields=array()) {
$where = "";
$values = array();
foreach ($unique_fields as $k => $v) {
if (!empty($where)) $where .= " && ";
$where .= "$k=?";
$values[] = $v;
}
$records = query("SELECT * FROM $table WHERE $where", $values);
if (false == $records) {
store($table, array_merge($unique_fields, $other_fields));
}
}

然后您可以创建一个迁移脚本,该脚本将根据您的规范更新任何环境。

我写了一个 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;
}

我认为笔记本电梯提供的答案是最好的... 但是因为没有人建议我使用的方法,我想我应该加入。大多数时候,我使用 phpMyAdmin 来设置和管理我的数据库。在其中,您可以简单地将复选标记放在所需的行旁边,然后在底部单击“ Export”并选择 SQL。它将为您选择的任何记录提供 INSERT 语句。希望这个能帮上忙。

笔记本升降机的代码运行良好,但有一些东西,我认为人们可能会喜欢。

数据库处理程序是一个参数,而不是硬编码的。使用了新的 mysql api。使用可选的 $where 参数替换 $id 以获得灵活性。使用 real _ escape _ string 以防有人尝试执行 sql 注入,并避免涉及引号的简单破坏。使用 INSERT table (field...) VALUES (value...)...语法,这样字段只定义一次,然后只列出每一行的值(内爆是可怕的)。因为奈杰尔约翰逊指出,我增加了 NULL处理。

我使用 $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).";";
}
?>

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

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

你可以使用 Sequel pro 来做到这一点,对于所获得的结果有一个“ get as insert statement”选项

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

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

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

  3. 接下来,使用架构右侧的列表框选择所需的表。

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

  5. 单击 Finish。

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

有了 PDO,你可以这样做。

$stmt = DB::getDB()->query("SELECT * FROM sometable", array());


$array = $stmt->fetchAll(PDO::FETCH_ASSOC);
$fields = array_keys($array[0]);


$statement = "INSERT INTO user_profiles_copy (".implode(",",$fields).") VALUES ";
$statement_values = null;


foreach ($array as $key => $post) {
if(isset($statement_values)) {
$statement_values .= ", \n";
}


$values = array_values($post);
foreach($values as $index => $value) {
$quoted = str_replace("'","\'",str_replace('"','\"', $value));
$values[$index] = (!isset($value) ? 'NULL' : "'" . $quoted."'") ;
}


$statement_values .= "(".implode(',',$values).")";




}
$statement .= $statement_values . ";";


echo $statement;

你可以用下面的代码创建一个 SP-它也支持 NULLS。

select 'my_table_name' into @tableName;


/*find column names*/
select GROUP_CONCAT(column_name SEPARATOR ', ') from information_schema.COLUMNS
where table_schema =DATABASE()
and table_name = @tableName
group by table_name
into @columns
;


/*wrap with IFNULL*/
select replace(@columns,',',',IFNULL(') into @selectColumns;
select replace(@selectColumns,',IFNULL(',',\'~NULL~\'),IFNULL(') into @selectColumns;


select concat('IFNULL(',@selectColumns,',\'~NULL~\')') into @selectColumns;


/*RETRIEVE COLUMN DATA FIELDS BY PK*/
SELECT
CONCAT(
'SELECT CONCAT_WS(','''\'\',\'\''',' ,
@selectColumns,
') AS all_columns FROM ',@tableName, ' where id = 5 into @values;'
)
INTO @sql;


PREPARE stmt FROM @sql;
EXECUTE stmt;


/*Create Insert Statement*/
select CONCAT('insert into ',@tableName,' (' , @columns ,') values (\'',@values,'\')') into @prepared;


/*UNWRAP NULLS*/
select replace(@prepared,'\'~NULL~\'','NULL') as statement;

在 MySQLWorkbench 中,您可以将任何单表查询的结果作为 INSERT语句列表导出。只要运行查询,然后:

Snapshot of export button

  1. 点击结果上方 Export/Import附近的软盘
  2. 给目标文件命名
  3. 在窗口底部,为 Format选择 SQL INSERT statements
  4. Save
  5. Export

如果想为表获取“ insert 语句”,可以尝试下面的代码。

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`;

结果,你会得到 iners 语句:

插入到 your_table(field_1field_2...field_n) VALUES (value _ 11,value _ 12,... ,value _ 1n) ;

插入到 your_table(field_1field_2...field_n) VALUES (value _ 21,value _ 22,... ,value _ 2n) ;

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

插入到 your_table(field_1field_2...field_n) VALUES (value _ m1,value _ m2,... ,value _ mn) ;

,其中 m-number 为 _ table 中的记录数

对于 HediSQL 用户 :

如果使用 HediSQL,则可以选择希望插入语句的行。然后右键单击 > Export grid rows > 选择“ Copy to clipboard”作为“ Output target”,选择“ Selection”作为“ Row Selection”,这样就不会导出其他行,“ SQL INSERTs”作为“ Output format”> 单击 OK。

enter image description here

插入语句将出现在剪贴板中。

在 PHPMyAdmin 中,你可以:

  1. 单击想要知道其插入语句的行上的副本。 SQL:

Select copy

  1. 单击“预览 SQL:”

Select Preview

  1. 您将获得生成它的已创建的 insert 语句

如果选择多行并从表的底部单击 copy,然后单击 Preview SQL,则可以一次对多行应用该方法

有一个非常简单和有用的解决方案,可以创建一个用于编辑的 INSERT 语句,而不需要通过复制和粘贴(剪贴板)导出 SQL:

  • 在 MySQLWorkbench 的查询结果窗口中选择该行,甚至可能选择几行。即使该行包含的数据与我想要插入到脚本中的数据不同,或者当目标是使用?占位符。
  • 将剪贴板中现在复制的行粘贴到同一个表中(查询结果列表是工作台中的编辑器) ,将其粘贴到最后一个空闲行。
  • 按下“ Apply”,将打开一个窗口,显示 INSERT 语句-DO NOT EXECUTE
  • 将 SQL 从窗口复制到剪贴板
  • 取消执行,因此不会更改数据库,而是将 SQL 保留在剪贴板中。
  • 将 SQL 粘贴到任何您想要的位置,并根据您的喜好进行编辑,例如插入? 占位符

下面的命令将转储到终端,而没有 Mysqldump插入周围输出的所有额外内容。这允许从终端进行复制,而无需写入文件。如果环境限制编写新文件,这将非常有用。

mysqldump -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10" --compact --no-create-info --complete-insert --quick

使用 mysqldump --help我发现了以下选项。

不要缓冲查询,直接转储到标准输出。 (默认为打开; 使用—— Skip-fast 来禁用。)

-t, --no-create-info 不要写表创建信息。

-c, --complete-insert 使用完整的 insert 语句。

减少冗长的输出(对调试很有用)。禁用 结构注释和页眉/页脚结构 选项——跳过-添加-放置表——跳过-添加-锁 ——跳过注释——跳过禁用键——跳过设置字符集。

如果您使用 phpMyAdmin (在版本5.x 上测试) :

点击“编辑”按钮旁边的行,你想有一个插入语句,然后在底部旁边的动作按钮只是选择“显示插入查询”和按“去”。

你可以试试这个

function get_insert_query($pdo, $table, $where_sth)
{
$sql = "";
$row_data = $pdo->query("SELECT * FROM `{$table}` WHERE $where_sth")->fetch();
if($row_data){
$sql = "INSERT INTO `$table` (";


foreach($row_data as $col_name => $value){
$sql .= "`".$col_name."`, ";
}
$sql = rtrim($sql, ", ");


$sql .= ") VALUES (";


foreach($row_data as $col_name => $value){
if (is_string($value)){
$value = $pdo->quote($value);
} else if ($value === null){
$value = 'NULL';
}


$sql .= $value .", ";
}
$sql = rtrim($sql, ", ");
$sql .= ");";
}


return $sql;
}

要使用它,只需拨打:

$pdo = new PDO( "connection string goes here" );
$sql = get_insert_query($pdo, 'texts', "text_id = 959");
echo $sql;

在 PhpMyAdmin 更新当前寄存器的 get insert 语句:

  1. 从 DB 中选择要从中获取寄存器的表
  2. “导出”标签的顶部菜单作为下面的图像

enter image description here

  1. 海关出口

  2. 下移到“数据创建选项”

enter image description here

到达目的地后,选择“ Insert”函数,按照您喜欢的语法执行

很简单。您所要做的就是在一个静态块中编写一个 Insert 语句,并将其作为一个整体块运行。

例如。如果希望从表(比如 ENGINEER _ DETAILS)中获取选定行的 Insert 语句,则必须运行此块-


spool
set sqlformat insert
select * from ENGINEER_DETAILS where engineer_name like '%John%';
spool off;

该块的输出将是 Insert 语句。