mysql - functions - select syntax

Obtener instrucción de inserción para la fila existente en MySQL

Question

Usando MySQL puedo ejecutar la consulta:

SHOW CREATE TABLE MyTable;

Y devolverá la declaración create table para la tabla específica. Esto es útil si ya tiene una tabla creada y desea crear la misma tabla en otra base de datos.

¿Es posible obtener la instrucción de inserción para una fila o un conjunto de filas ya existente? Algunas tablas tienen muchas columnas, y sería bueno para mí poder obtener una declaración de inserción para transferir filas a otra base de datos sin tener que escribir la declaración de inserción, o sin exportar los datos a CSV y luego importar los mismos datos en la otra base de datos.

Solo para aclarar, lo que quiero es algo que funcione de la siguiente manera:

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

Y me han devuelto lo siguiente:

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

Answer 1

Como ha copiado la tabla con el SQL producido por SHOW CREATE TABLE MyTable , puede hacer lo siguiente para cargar los datos en la nueva tabla.

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

Si realmente quieres las instrucciones INSERT, entonces la única forma que conozco es usar mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.htm . Puede darle opciones para simplemente volcar datos para una tabla específica e incluso para limitar filas.

Answer 2

Con PDO puedes hacerlo de esta manera.

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

Answer 3

Creo que la respuesta proporcionada por Laptop Lifts es la mejor ... pero dado que nadie sugirió el enfoque que uso, pensé que debería intervenir. Utilizo phpMyAdmin para configurar y administrar mis bases de datos la mayor parte del tiempo. En él, simplemente puede colocar marcas de verificación junto a las filas que desee, y en la parte inferior haga clic en "Exportar" y seleccione SQL. Le dará instrucciones INSERT para los registros que haya seleccionado. Espero que esto ayude.

Answer 4

Dentro del banco de trabajo MySQL realice lo siguiente:

1. Haga clic en Servidor> Exportar datos

2. En la pestaña Selección de objetos, seleccione el esquema deseado.

3. A continuación, seleccione las tablas deseadas utilizando el cuadro de lista a la derecha del esquema.

4. Seleccione una ubicación de archivo para exportar la secuencia de comandos.

5. Haga clic en Finalizar.

6. Navegue hasta el archivo recién creado y copie las instrucciones de inserción.

Answer 5

El código de Laptop Lift funciona bien, pero había algunas cosas que pensé que a la gente podría gustarles.

El manejador de base de datos es un argumento, no está codificado. Usó la nueva API de MySQL. Se reemplazó $ id con un argumento $ opcional para flexibilidad. Utiliza real_escape_string en caso de que alguien haya intentado alguna vez inyectar sql y para evitar roturas simples que implican comillas. Se utilizó la INSERT table (field...) VALUES (value...)... sintaxis para que los campos se definan solo una vez y luego se enumeren los valores de cada fila (la implosión es impresionante). Como Nigel Johnson lo señaló, agregué manejo NULL .

Usé $array[$key] porque me preocupaba que de alguna manera pudiera cambiar, pero a menos que algo esté terriblemente mal, no debería ser así.

<?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).";";
}
?>

Answer 6

En MySQL Workbench puede exportar los resultados de cualquier consulta de tabla única como una lista de INSERT . Simplemente ejecute la consulta y luego:

1. haga clic en el disquete cerca de Export/Import arriba de los resultados
2. darle un nombre al archivo de destino
3. en la parte inferior de la ventana, para Format seleccione SQL INSERT statements
4. haga clic en Save
5. click Export

Answer 7

En función de sus comentarios, su objetivo es migrar los cambios de la base de datos de un entorno de desarrollo a un entorno de producción.

La mejor manera de hacerlo es mantener los cambios en su base de datos en su código fuente y, en consecuencia, seguirlos en su sistema de control de origen, como git o svn.

puede ponerse en marcha rápidamente con algo como esto: https://github.com/davejkiger/mysql-php-migrations

como una solución personalizada muy básica en PHP, puedes usar una función como esta:

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

luego puede crear un script de migración que actualizará cualquier entorno según sus especificaciones.

Answer 8

Escribí una función php que hará esto. Necesitaba hacer una instrucción de inserción en caso de que un registro deba ser reemplazado después de la eliminación de una tabla de historial:

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

Answer 9

No parece haber una manera de obtener las INSERT desde la consola MySQL, pero puedes obtenerlas usando mysqldump como sugirió Rob. Especifique -t para omitir la creación de la tabla.

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

Answer 10

Puede crear un SP con el siguiente código: también admite 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;

Answer 11

Si desea obtener "insertar declaración" para su tabla, puede probar el siguiente código.

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

Como resultado, tendrá una declaración de 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_21, value_22, ..., value_2n);

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

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

, donde m - número de registros en su_tabla

Answer 12

Uso el programa SQLYOG donde puedo hacer una consulta de selección, señalar los resultados y elija exportar como sql. Esto me da las instrucciones de inserción.

Answer 13

puede usar Sequel pro para hacer esto, hay una opción para ''obtener como instrucción de inserción'' para los resultados obtenidos