Saya mulai dengan googling, dan menemukan ini artikel yang berbicara tentang mutex tabel.
Saya memiliki sebuah meja dengan ~14 juta catatan. Jika saya ingin menambahkan lebih banyak data dalam format yang sama, apakah ada cara untuk memastikan rekor saya ingin menyisipkan sudah tidak ada tanpa menggunakan sepasang query (yaitu, satu query untuk memeriksa dan satu untuk menyisipkan merupakan hasil set kosong)?
Apakah yang unik
kendala di lapangan menjamin insert
akan gagal jika itu's sudah ada?
Tampaknya bahwa dengan hanya kendala, ketika saya mengeluarkan menyisipkan via php, script croaks.
gunakan INSERT MENGABAIKAN KE meja
lihat http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html
ada's juga INSERT ... ON DUPLICATE KEY UPDATE
sintaks, anda dapat menemukan penjelasan tentang dev.mysql.com
Posting dari bogdan.org.ua menurut Google's webcache:
18 november 2007
Untuk memulai: sebagai terbaru MySQL, sintaks disajikan dalam judul tidak mungkin. Tapi ada beberapa yang sangat mudah cara untuk mencapai apa yang diharapkan menggunakan fungsi yang ada.
Ada 3 kemungkinan solusi: dengan menggunakan INSERT MENGABAIKAN, MENGGANTI, atau MASUKKAN ... ON DUPLICATE KEY UPDATE.
Bayangkan kita memiliki sebuah tabel:
CREATE TABLE
transkrip
(ensembl_transcript_id
varchar(20) NOT NULL,transcript_chrom_start
int(10) unsigned NOT NULL,transcript_chrom_end
int(10) unsigned NOT NULL, PRIMARY KEY (ensembl_transcript_id
) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Sekarang bayangkan bahwa kita memiliki otomatis pipa mengimpor transkrip meta-data dari Ensembl, dan yang karena berbagai alasan pipa mungkin rusak setiap langkah eksekusi. Dengan demikian, kita perlu memastikan dua hal-hal: 1) eksekusi berulang pipa tidak akan menghancurkan kita database, dan 2) diulang eksekusi tidak akan mati karena 'duplikat primary key' kesalahan.
Metode 1: menggunakan MENGGANTI
Ini sangat sederhana:
GANTI MENJADI
transkrip
SETensembl_transcript_id
= 'ENSORGT00000000001',transcript_chrom_start
= 12345,transcript_chrom_end
= 12678;Jika catatan yang ada, maka akan ditimpa; jika belum ada, maka akan dibuat. Namun, dengan menggunakan metode ini tidak efisien untuk kasus kami: kami tidak perlu untuk menimpa data yang ada, itu baik-baik saja hanya untuk melewati mereka.
Metode 2: dengan menggunakan INSERT MENGABAIKAN Juga sangat sederhana:
MENYISIPKAN MENGABAIKAN KE
transkrip
SETensembl_transcript_id
= 'ENSORGT00000000001',transcript_chrom_start
= 12345,transcript_chrom_end
= 12678;di Sini, jika 'ensembl_transcript_id' sudah hadir di database, ia akan diam-diam dilewati (diabaikan). (Untuk lebih tepatnya, berikut adalah kutipan dari MySQL reference manual: "Jika anda menggunakan MENGABAIKAN kata kunci, kesalahan yang terjadi saat menjalankan MENYISIPKAN pernyataan diperlakukan sebagai peringatan bukan. Misalnya, tanpa MENGABAIKAN, baris yang duplikat yang ada UNIK indeks atau nilai KUNCI PRIMER pada tabel penyebab duplikat kunci kesalahan dan pernyataan dibatalkan.".) Jika rekor belum ada, maka akan dibuat.
Ini kedua metode ini memiliki beberapa kelemahan potensial, termasuk non-aborsi dari query dalam kasus lain terjadi masalah (lihat manual). Oleh karena itu harus digunakan jika sebelumnya diuji tanpa MENGABAIKAN kata kunci.
Ada satu pilihan: untuk menggunakan
INSERT ... ON DUPLICATE KEY UPDATE
sintaks, dan di bagian PEMBARUAN hanya melakukan apa-apa yang tak berarti (kosong) operasi, seperti menghitung 0+0 (Geoffray menyarankan melakukan id=id tugas untuk MySQL optimasi mesin untuk mengabaikan ini operasi). Keuntungan dari metode ini adalah bahwa hal itu hanya mengabaikan duplikat peristiwa penting, dan masih dibatalkan pada kesalahan-kesalahan lainnya.Sebagai pemberitahuan akhir: posting ini terinspirasi oleh Xaprb. Saya juga menyarankan untuk berkonsultasi dengan pos lain pada tulisan yang fleksibel query SQL.
Solusi:
INSERT INTO `table` (`value1`, `value2`)
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table`
WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1)
Penjelasan:
Terdalam query
SELECT * FROM `table`
WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1
digunakan sebagai DIMANA TIDAK ADA
-kondisi mendeteksi jika ada sudah ada baris dengan data yang akan dimasukkan. Setelah satu baris semacam ini ditemukan, query mungkin berhenti, maka LIMIT 1
(micro-optimasi, dapat dihilangkan).
Menengah query
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
merupakan nilai-nilai yang akan dimasukkan. DUAL
mengacu pada satu baris, satu kolom tabel hadir secara default di semua database Oracle (lihat https://en.wikipedia.org/wiki/DUAL_table). Pada MySQL-Server versi 5.7.26 aku punya permintaan yang sah ketika menghilangkan DUAL
, tetapi versi yang lebih tua (seperti 5.5.60) tampaknya memerlukan DARI
informasi. Dengan menggunakan DIMANA TIDAK ADA
intermediate permintaan kembali kosong akibat mengatur jika terdalam query ditemukan pencocokan data.
Permintaan luar
INSERT INTO `table` (`value1`, `value2`)
memasukkan data, jika ada yang dikembalikan oleh intermediate query.
on duplicate key update, atau insert ignore dapat menjadi solusi yang layak dengan MySQL.
Contoh on duplicate key update update berdasarkan mysql.com
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
Contoh dari insert ignore berdasarkan mysql.com
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Atau:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Atau:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Sederhana kendala harus melakukan pekerjaan itu, jika pengecualian yang dapat diterima. Contoh :
Maaf ini tampaknya sederhana. Aku tahu itu terlihat buruk dihadapkan ke link anda berbagi dengan kami. ;-(
Tapi aku neverleless memberikan jawaban ini, karena tampaknya untuk mengisi kebutuhan anda. (Jika tidak, hal ini dapat memicu anda memperbarui kebutuhan anda, yang akan menjadi "Bagus"(TM) juga).
Edited: Jika insert akan istirahat database batasan unik, pengecualian melemparkan di tingkat database, disampaikan oleh pengemudi. Itu pasti akan berhenti naskah anda, dengan kegagalan. Itu harus mungkin di PHP ke alamat yang terjadi ...
Berikut ini adalah fungsi PHP yang akan menyisipkan baris hanya jika semua kolom yang ditetapkan nilai don't sudah ada di meja.
Jika salah satu kolom yang berbeda, baris yang akan ditambahkan.
Jika tabel kosong, baris yang akan ditambahkan.
Jika turut ada di mana semua kolom yang ditetapkan memiliki nilai-nilai tertentu, yang turut memenangkan't akan ditambahkan.
fungsi insert_unique($table, $var) { if (count($var)) { $tabel = mysql_real_escape_string($tabel); $var = array_map('mysql_real_escape_string', $var);
$req = "INSERT INTO $table
(". bergabung('
, ', array_keys($var)) ."
) ";
$req .= "PILIH ',". bergabung("', '", $var) .",' DARI GANDA ";
$req .= "di MANA TIDAK ADA (PILIH 1 DARI $tabel
MANA ";
foreach ($var AS $col => $val)
$req .= "$col
='$val' DAN ";
$req = substr($req, 0, -5) . ") LIMIT 1";
$res = mysql_query($req) OR die(); kembali mysql_insert_id(); }
return False; }
Contoh penggunaan :
<?php
insert_unique('mytable', array(
'mycolumn1' => 'myvalue1',
'mycolumn2' => 'myvalue2',
'mycolumn3' => 'myvalue3'
)
);
?>
Coba yang berikut ini:
IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
INSERT INTO beta (name) VALUES ('John')
INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END
Ada beberapa jawaban yang mencakup bagaimana untuk memecahkan masalah ini jika anda memiliki UNIK
index yang anda dapat memeriksa melawan dengan ON DUPLICATE KEY
atau INSERT IGNORE
. Bahwa ini tidak selalu terjadi, dan sebagai UNIK
memiliki suhu udara turun menjadi kendala (1000 bytes) anda mungkin tidak bisa mengubah itu. Misalnya, saya harus bekerja dengan metadata di WordPress (wp_postmeta
).
Saya akhirnya diselesaikan dengan dua pertanyaan:
UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);
Query 1 reguler UPDATE
query dengan tidak ada efek ketika dataset yang dimaksud adalah tidak ada. Query 2 adalah sebuah INSERT
yang tergantung pada sebuah TIDAK ADA
, yaitu INSERT
ini hanya dijalankan ketika dataset doesn't ada.
Coba:
// Check if exist cod = 56789
include "database.php";
$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
if($countrows == '1')
{
// Exist
}
else
{
// .... Not exist
}
Atau anda dapat melakukan:
// Check if exist cod = 56789
include "database.php";
$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
while($result = mysql_fetch_array($querycheck))
{
$xxx = $result['xxx'];
if($xxx == '56789')
{
// Exist
}
else
{
// Not exist
}
}
Metode ini lebih cepat dan mudah. Untuk meningkatkan kecepatan query di meja besar INDEKS kolom 'xxx' ( Dalam contoh saya ).
Sesuatu yang patut dicatat adalah bahwa MENYISIPKAN MENGABAIKAN masih akan increment primary key apakah pernyataan itu sukses atau tidak seperti yang normal akan MENYISIPKAN.
Hal ini akan menyebabkan kesenjangan dalam kunci utama yang mungkin membuat seorang programmer yang tidak stabil secara mental. Atau jika anda aplikasi ini dirancang dengan buruk dan tergantung pada pilihan tambahan kunci primer, itu mungkin menjadi sakit kepala.
Melihat ke innodb_autoinc_lock_mode = 0
(pengaturan server, dan dilengkapi dengan kinerja sedikit hit), atau gunakan PILIH terlebih dahulu untuk memastikan permintaan anda tidak akan gagal (yang juga hadir dengan performa dan kode tambahan).