Pobierz instrukcję wstawiania dla istniejącego wiersza w MySQL

Za pomocą MySQL mogę uruchomić zapytanie:

SHOW CREATE TABLE MyTable;

I zwróci instrukcję tworzenia tabeli dla określonej tabeli. Jest to przydatne, jeśli masz już utworzoną tabelę i chcesz utworzyć tę samą tabelę w innej bazie danych.

Czy można uzyskać instrukcję wstawiania dla już istniejącego wiersza lub zestawu wierszy? Niektóre tabele mają wiele kolumn i byłoby miło móc uzyskać instrukcję wstawiania, aby przenieść wiersze do innej bazy danych bez konieczności zapisywania instrukcji wstawiania lub bez eksportowania danych do CSV, a następnie importowania tych samych danych do innej bazy danych.

Aby wyjaśnić, czego chcę, to coś, co działałoby w następujący sposób:

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

I zwróciły mi:

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

Wydaje się, że nie ma sposobu na pobranie INSERTinstrukcji z konsoli MySQL, ale możesz je pobrać za pomocą mysqldump, jak zasugerował Rob. Określ, -taby pominąć tworzenie tabeli.

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

W MySQL Workbench możesz wyeksportować wyniki dowolnego zapytania jednotabelowego jako listę INSERTinstrukcji. Po prostu uruchom zapytanie, a następnie:

1. kliknij znajdującą się w pobliżu dyskietkę Export/Import wyników
2. nadaj plikowi docelowemu nazwę
3. u dołu okna do FormatwyboruSQL INSERT statements
4. Kliknij Save
5. Kliknij Export

Ponieważ skopiowałeś tabelę za pomocą kodu SQL utworzonego przez SHOW CREATE TABLE MyTable , możesz po prostu wykonać następujące czynności, aby załadować dane do nowej tabeli.

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

Jeśli naprawdę potrzebujesz instrukcji INSERT, jedyny znany mi sposób to użycie mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.htm . Możesz dać mu opcje, aby po prostu zrzucić dane dla określonej tabeli, a nawet ograniczyć wiersze.

Napisałem funkcję php, która to zrobi. Musiałem zrobić instrukcję wstawiania na wypadek, gdyby rekord musiał zostać zastąpiony po usunięciu dla tabeli historii:

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

Kod podnośnika laptopa działa dobrze, ale było kilka rzeczy, które moim zdaniem mogą się spodobać.

Obsługa bazy danych jest argumentem, a nie zakodowanym na stałe. Użyto nowego mysql api. Zastąpiono $ id opcjonalnym argumentem $ where na rzecz elastyczności. Użyto real_escape_string na wypadek, gdyby ktoś próbował kiedykolwiek wykonać wstrzyknięcie sql i uniknąć prostych błędów związanych z cudzysłowami. Użyłem INSERT table (field...) VALUES (value...)...składni, aby pola były zdefiniowane tylko raz, a następnie po prostu wypisały wartości każdego wiersza (implode jest niesamowity). Ponieważ Nigel Johnson to zauważył, dodałem NULLobsługę.

Użyłem, $array[$key]ponieważ obawiałem się, że może się to jakoś zmienić, ale chyba że coś jest okropnie nie tak, i tak nie powinno.

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

Używam programu SQLYOG, w którym mogę wykonać zapytanie wybierające, wskazać wyniki i wybrać eksport jako sql. To daje mi instrukcje wstawiania.

W ramach stanowiska roboczego MySQL wykonaj następujące czynności:

1. Kliknij opcję Serwer> Eksport danych

2. W zakładce Object Selection wybierz żądany schemat.

3. Następnie wybierz żądane tabele, używając pola listy po prawej stronie schematu.

4. Wybierz lokalizację pliku, aby wyeksportować skrypt.

5. Kliknij Finish.

6. Przejdź do nowo utworzonego pliku i skopiuj instrukcje wstawiania.

Jeśli chcesz uzyskać instrukcję wstawiania dla swojej tabeli, możesz wypróbować poniższy kod.

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

W rezultacie otrzymasz oświadczenie wstawiające:

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

, gdzie m - liczba rekordów w twojej_tabeli

możesz użyć do tego Sequel pro, istnieje opcja „get as insert statement” dla uzyskanych wyników

W PHPMyAdmin możesz:

1. kliknij kopiuj wiersz, który chcesz poznać jego instrukcje wstawiania SQL:

2. kliknij Podgląd SQL:

3. otrzymasz utworzoną instrukcję wstawiania, która ją generuje

Możesz to zastosować do wielu wierszy jednocześnie, jeśli wybierzesz je i klikniesz kopiuj od dołu tabeli, a następnie Podgląd SQl

Dzięki PDO możesz to zrobić w ten sposób.

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

Możesz utworzyć SP za pomocą poniższego kodu - obsługuje również 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;

Dla użytkowników HeidiSQL :

Jeśli używasz HeidiSQL, możesz wybrać wiersz (wiersze), w których chcesz otrzymać instrukcję wstawiania. Następnie kliknij prawym przyciskiem myszy> Eksportuj wiersze siatki> wybierz „Kopiuj do schowka” dla „Cel wyjściowy”, „Zaznaczenie” dla „Wybór wierszy”, aby nie eksportować innych wierszy, „WSTAWIENIE SQL” dla „Format wyjściowy”> Kliknij OK.

Instrukcja wstawiania będzie znajdować się w schowku.

Myślę, że odpowiedź udzielona przez Laptop Lifts jest najlepsza ... ale ponieważ nikt nie zasugerował podejścia, którego używam, pomyślałem, że powinienem zadzwonić. Używam phpMyAdmin do konfigurowania i zarządzania moimi bazami danych przez większość czasu. W nim możesz po prostu umieścić znaczniki wyboru obok żądanych wierszy, a na dole kliknąć „Eksportuj” i wybrać SQL. Podaje instrukcje INSERT dla dowolnych wybranych rekordów. Mam nadzieję że to pomoże.

Na podstawie Twoich komentarzy Twoim celem jest migracja zmian w bazie danych ze środowiska programistycznego do środowiska produkcyjnego.

Najlepszym sposobem na to jest zachowanie zmian w bazie danych w kodzie źródłowym i śledzenie ich w systemie kontroli źródła, takim jak git lub svn.

możesz szybko rozpocząć pracę za pomocą czegoś takiego: https://github.com/davejkiger/mysql-php-migrations

jako bardzo podstawowe niestandardowe rozwiązanie w PHP możesz użyć takiej funkcji:

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

następnie możesz utworzyć skrypt migracji, który zaktualizuje dowolne środowisko zgodnie z Twoimi specyfikacjami.