Получить вставку для существующей строки в MySQL

Используя MySQL, я могу выполнить запрос:

SHOW CREATE TABLE MyTable;

И он вернет инструкцию create table для конкретной таблицы. Это полезно, если у вас уже создана таблица и вы хотите создать ту же таблицу в другой базе данных.

Можно ли получить инструкцию insert для уже существующей строки или набора строк? В некоторых таблицах много столбцов, и мне было бы удобно получить инструкцию 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');

Ответы

Ответ 1

Кажется, что нет способа получить инструкции INSERT на консоли MySQL, но вы можете получить их с помощью mysqldump, например Роба. Задайте -t, чтобы опустить создание таблицы.

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

Ответ 2

В MySQL Workbench вы можете экспортировать результаты любого запроса в одну таблицу в виде списка операторов INSERT. Просто запустите запрос, а затем:

Snapshot of export button

  1. нажмите на дискету рядом с Export/Import над результатами
  2. дать имя целевому файлу
  3. в нижней части окна для выбора Format выберите SQL INSERT statements
  4. нажмите Save
  5. нажмите Export

Ответ 3

Поскольку вы скопировали таблицу с SQL, созданной с помощью SHOW CREATE TABLE MyTable, вы можете просто сделать следующее, чтобы загрузить данные в новую таблицу.

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. Вы можете дать ему возможность просто выгружать данные для конкретной таблицы и даже ограничивать строки.

Ответ 4

Я написал php-функцию, которая сделает это. Мне нужно было сделать инструкцию insert в случае, если запись должна быть заменена после удаления для таблицы истории:

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

Ответ 5

Laptop Lift code отлично работает, но было несколько вещей, которые, как я полагал, могут понравиться людям.

Обработчик базы данных - это аргумент, а не hardcoded. Используется новый mysql api. Заменил $id дополнительным аргументом $where для гибкости. Используется real_escape_string в случае, если кто-либо когда-либо пытался сделать SQL-инъекцию и избегать простых обрывов, связанных с кавычками. Используется синтаксис INSERT table (field...) VALUES (value...)..., так что поля определяются только один раз, а затем просто списывают значения каждой строки (implode является удивительным). Поскольку Найджел Джонсон указал на это, я добавил обработку 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).";";
}
?>

Ответ 6

Я использую программу SQLYOG, где я могу сделать запрос выбора, указать на screenshot результаты и выбрать экспорт как sql. Это дает мне инструкции для вставки.

Ответ 7

В рабочей среде MySQL выполните следующие действия:

  • Нажмите "Сервер" > "Экспорт данных"

  • На вкладке "Выбор объекта" выберите нужную схему.

  • Затем выберите нужные таблицы, используя поле списка справа от схемы.

  • Выберите расположение файла для экспорта script.

  • Нажмите "Готово".

  • Перейдите к вновь созданному файлу и скопируйте инструкции вставки.

Ответ 8

Если вы хотите получить "инструкцию 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`;

В результате у вас будет инструкция insers:

INSERT INTO your_table (field_1, field_2, ..., field_n) VALUES (значение_11, значение_12,..., значение_1n);

INSERT INTO your_table (field_1, field_2, ..., field_n) VALUES (значение_21, значение_22,..., значение_2n);

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

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

где m - количество записей в вашей таблице

Ответ 9

вы можете использовать Sequel pro для этого, есть опция "получить как инструкцию вставки" для полученных результатов

Ответ 10

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

Ответ 11

Вы можете создать 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;

Ответ 12

Я думаю, что ответ, предоставленный Laptop Lifts, является лучшим... но поскольку никто не предлагал подход, который я использую, я решил, что должен перезвонить. Я использую phpMyAdmin для настройки и управления моими базами большую часть времени. В нем вы можете просто поставить галочки рядом с нужными строками, а внизу нажать "Экспорт" и выбрать SQL. Он предоставит вам инструкции INSERT для выбранных вами записей. Надеюсь, это поможет.

Ответ 13

Для пользователей HeidiSQL:

Если вы используете HeidiSQL, вы можете выбрать строки, которые вы хотите получить оператором вставки. Затем щелкните правой кнопкой мыши> Экспортировать строки сетки> выберите "Копировать в буфер" для "Выходной объект", "Выбор" для "Выбор строки", чтобы не экспортировать другие строки, "SQL INSERT" для "Формат вывода"> Нажмите "ОК".

enter image description here

Оператор вставки будет внутри вашего буфера обмена.

Ответ 14

На основе ваших комментариев ваша цель - перенести изменения базы данных из среды разработки в производственную среду.

Лучший способ сделать это - сохранить изменения базы данных в исходном коде и, следовательно, отслеживать их в исходной системе управления, например 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));
    }
}

то вы можете создать миграцию script, которая обновит любую среду по вашим спецификациям.