mysql - buat tabel jika tidak ada truncate lain?
<p><strong> ini pertanyaan terbaru: </strong></p><p>kueri saat ini melakukan sesuatu seperti:<br></p><pre><code>$sql1 = "truncate table fubar"; $sql2 = "create temporary table if not exists fubar select id, name from barfu"; </code></pre><p>pertama kali metode yang mengandung ini dijalankan, ia menghasilkan pesan kesalahan pada truncate karena tabel belum ada.</p><p>apakah satu-satunya pilihan saya untuk melakukan <code>create table</code>, menjalankan <code>truncate table</code>, dan kemudian mengisi tabel? (3 pertanyaan terpisah)</p><p><strong> pertanyaan asli adalah: </strong></p><p>saya mengalami kesulitan untuk mencari tahu apakah hal berikut ini dimungkinkan di mysql tanpa harus menulis blok sql:</p><pre><code>create table fubar if not exists else truncate table fubar </code></pre><p>jika saya menjalankan truncate secara terpisah sebelum membuat tabel, dan tabel tidak ada, maka saya mendapatkan pesan kesalahan. saya mencoba menghilangkan pesan kesalahan itu tanpa harus menambahkan pertanyaan lagi.</p><p>kode ini akan dieksekusi menggunakan php.</p>
<p>shmuel613, akan lebih baik untuk memperbarui pertanyaan awal anda daripada menjawab. lebih baik jika ada satu tempat yang berisi pertanyaan lengkap daripada menyebar dalam diskusi.</p><p>jawaban ben masuk akal, kecuali ia tampaknya memiliki 'tidak' di mana ia tidak menginginkannya. menjatuhkan tabel hanya jika<strong> tidak </strong>ada tidak tepat.</p><p>anda memang membutuhkan banyak pernyataan. buat dengan kondisional lalu isi:</p><ol> <li>buat tabel sementara jika tidak ada fubar (id int, name varchar (80))</li> <li>truncate table fubar</li> <li>masukkan ke dalam fubar pilih * dari barfu</li> </ol><p>atau cukup jatuhkan dan buat ulang</p><ol> <li>drop tabel jika ada fubar</li> <li>buat tabel sementara id select fubar, nama dari barfu</li> </ol><p>dengan sql murni itu adalah dua kelas solusi nyata anda. saya suka yang kedua lebih baik.</p><p>(dengan prosedur tersimpan anda bisa menguranginya menjadi satu pernyataan. sesuatu seperti: truncateandpopulate (fubar) tetapi pada saat anda menulis kode untuk truncateandpopulate () anda akan menghabiskan lebih banyak waktu daripada hanya menggunakan sql di atas.)</p>
<p>dari baris perintah linux</p><pre><code>for proc\_to\_kill in `mysql -h... -u... -p... -a --skip-column-names -e"show processlist" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -a --skip-column-names -e"kill query ${proc\_to\_kill}" ; done </code></pre><p>anda bisa mengubah opsi grep di header loop for untuk menemukan pengguna tertentu atau string tertentu dalam kueri.</p><p>jika anda memiliki mysql 5.1 di mana daftar proses ada di information\_schema, anda bisa melakukan ini untuk menghasilkan perintah kill query dalam jumlah besar dari dalam klien mysql:</p><pre><code>select group\_concat(concat('kill query ',id,';') separator ' ') killquery from information\_schema.processlist where user&lt;&gt;'system user'\g </code></pre><p>anda bisa melakukan klausa where terhadap bidang info untuk mencari kueri tertentu, bidang time terhadap kueri yang sudah berjalan lama, atau bidang db terhadap database tertentu.</p> <p>arsitektur innodb menuntut penggunaan empat tipe dasar halaman info</p><ul> <li>halaman data tabel</li> <li>halaman indeks tabel</li> <li>tabel metadata</li> <li> <a href="http://en.wikipedia.org/wiki/multiversion\_concurrency\_control"><strong> [~ # ~] mvcc [~ # ~] </strong></a> data (untuk mendukung isolasi transaksi dan <a href="http://en.wikipedia.org/wiki/acid"><strong> kepatuhan acid </strong></a> ) <ul> <li>segmen kembalikan</li> <li>batalkan spasi</li> <li>double write buffer (penulisan latar belakang untuk mencegah ketergantungan pada caching os)</li> <li>masukkan buffer (mengelola perubahan pada indeks sekunder yang tidak unik)</li> </ul> </li> </ul><p> <a href="http://www.scribd.com/doc/31337494/xtradb-innodb-internals-in-drawing"><strong> <em> lihat representasi pictorial dari ibdata1</em> </strong></a> </p><p>secara default, <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> dinonaktifkan. ini menyebabkan keempat jenis halaman info mendaratkan satu file bernama ibdata1. banyak orang mencoba menyebar data dengan membuat banyak file ibdata. ini dapat menyebabkan fragmentasi data dan halaman indeks.</p><p>inilah sebabnya saya sering <a href="https://stackoverflow.com/a/4056261/491757">merekomendasikan untuk membersihkan infrastruktur innodb, menggunakan file ibdata1 default dan tidak lebih</a> .</p><p>menyalin sangat berbahaya karena infrastruktur di mana innodb bekerja. ada dua infrastruktur dasar</p><ul> <li>innodb\_file\_per\_table dinonaktifkan</li> <li>innodb\_file\_per\_table diaktifkan</li> </ul><h1>innodb ( <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> dinonaktifkan)</h1><p>dengan <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> dinonaktifkan, semua jenis info innodb ini tinggal di dalam ibdata1. satu-satunya manifestasi dari setiap tabel innodb di luar ibdata1 adalah file .frm dari tabel innodb. menyalin semua data innodb sekaligus membutuhkan menyalin semua/var/lib/mysql.</p><p>menyalin tabel innodb individu sama sekali tidak mungkin. anda harus membuang mysql untuk mengekstrak dump tabel sebagai representasi logis dari data dan definisi indeks yang sesuai. anda kemudian akan memuat dump itu ke database lain di server yang sama atau server lain.</p><h1>innodb ( <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> diaktifkan)</h1><p>dengan <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> diaktifkan, data tabel dan indeksnya tinggal di folder database di sebelah file .frm. misalnya, untuk tabel db1.mytable, manifestasi dari tabel innodb di luar ibdata1 adalah:</p><ul> <li><code>/var/lib/mysql/db1/mytable.frm</code></li> <li><code>/var/lib/mysql/db1/mytable.ibd</code></li> </ul><h1>tablespace sistem <code>ibdata1</code></h1><p>semua metadata untuk db1.mytable masih berada di ibdata1 dan <em> sama sekali tidak ada jalan lain</em>. redo log dan data mvcc juga masih hidup dengan ibdata1.</p><p>ketika datang ke tabel fragmentasi, inilah yang terjadi pada ibdata1:</p><ul> <li><strong> <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> diaktifkan </strong>: anda dapat mengecilkan db1.mytables dengan <code>alter table db1.mytable engine=innodb;</code> atau <code>optimize table db1.mytable;</code>. ini menghasilkan /var/lib/mysql/db1/mytable.ibd secara fisik lebih kecil tanpa fragmentasi.</li> <li><strong> <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar\_innodb\_file\_per\_table">innodb\_file\_per\_table</a> dinonaktifkan </strong>: anda tidak dapat mengecilkan db1.mytables dengan <code>alter table db1.mytable engine=innodb;</code> atau <code>optimize table db1.mytable;</code> karena berada dengan ibdata1. menjalankan perintah mana pun sebenarnya, membuat tabel berdekatan dan lebih cepat untuk membaca dan menulis. sayangnya, itu terjadi pada akhir ibdata1. ini membuat ibdata1 tumbuh dengan cepat. <a href="https://stackoverflow.com/a/4056261/491757">ini sepenuhnya dibahas di pos pembersihan innodb saya</a> .</li> </ul><h1>peringatan (atau bahaya sebagai <a href="http://www.youtube.com/watch?v=rg0ochx16dg&amp;feature=related">robot akan mengatakan dalam lost in space</a> )</h1><p>jika anda berpikir untuk hanya menyalin file .frm dan .ibd, anda sejalan dengan dunia yang menyakitkan. menyalin file .frm dan .ibd dari tabel innodb hanya baik <strong> <em> jika dan hanya jika anda dapat menjamin bahwa id tablespace dari file .ibd cocok persis dengan entri tablespace id di metadata dari file ibdata1</em> </strong>.</p><p>saya menulis dua posting di dba stackexchange tentang konsep id tablespace ini</p><ul> <li> <a href="https://dba.stackexchange.com/questions/9500/table-compression-in-innodb/9555#9555">kompresi tabel dalam innodb?</a> (di bawah judul 'memulihkan database')</li> <li> <a href="https://dba.stackexchange.com/questions/6268/how-to-recover-an-innodb-table-whose-files-were-moved-around/6269#6269">cara memulihkan tabel innodb yang file-nya dipindahkan</a> </li> </ul><p>berikut ini adalah tautan yang sangat baik tentang cara memasang kembali file .ibd ke ibdata1 jika id tablespace tidak cocok: <a href="http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file">http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in- file</a> . setelah membaca ini, anda harus segera menyadari bahwa menyalin file .ibd benar-benar gila.</p><p>untuk innodb, anda hanya perlu memindahkan sesuatu ini</p><pre><code>create table db2.mytable like db1.mytable; insert into db2.mytable select * from db1.mytable; </code></pre><p>untuk membuat salinan tabel innodb.</p><p>jika anda memigrasikannya ke server db lain, gunakan mysqldump.</p><p>sehubungan dengan mencampur semua tabel innodb dari semua database, saya benar-benar dapat melihat kebijaksanaan dalam melakukannya. di perusahaan hosting db/web perusahaan saya, saya memiliki satu klien mysql yang memiliki tabel di satu basis data yang batasannya dipetakan ke tabel lain di basis data lain dalam instance mysql yang sama. dengan satu repositori metadata yang umum, itu membuat dukungan transaksional dan pengoperasian mvcc mungkin di beberapa basis data.</p> <p>jika anda membuat kolom sebagai <code>serial</code> postgresql secara otomatis membuat urutan untuk itu.</p><p>nama urutannya dibuat secara otomatis dan selalu tablename\_columnname\_seq, dalam kasus anda urutannya adalah nama <code>names\_id\_seq</code>.</p><p>setelah memasukkan ke dalam tabel, anda dapat memanggil <code>currval()</code> dengan nama urutan itu:</p><pre><code>postgres=&gt; create table names in schema\_name (id serial, name varchar(20)); create table postgres=&gt; insert into names (name) values ('arthur dent'); insert 0 1 postgres=&gt; select currval('names\_id\_seq'); currval  1 (1 row) postgres=&gt; </code></pre><p>alih-alih meng-hardcoding nama urutan, anda juga dapat menggunakan <code>pg\_get\_serial\_sequence()</code> sebagai gantinya:</p><pre><code>select currval(pg\_get\_serial\_sequence('names', 'id')); </code></pre><p>dengan begitu anda tidak perlu bergantung pada strategi penamaan yang digunakan postgres.</p><p>atau jika anda tidak ingin menggunakan nama urutan sama sekali, gunakan <code>lastval()</code></p> <p>statistik hanyalah bentuk metadata dinamis yang membantu pengoptimal kueri dalam membuat keputusan yang lebih baik. misalnya, jika hanya ada selusin baris dalam sebuah tabel, maka tidak ada gunanya pergi ke indeks untuk melakukan pencarian; anda akan selalu lebih baik melakukan pemindaian tabel penuh. tetapi jika tabel yang sama tumbuh hingga sejuta baris, maka anda mungkin akan lebih baik menggunakan indeks. tetapi jika anda kueri tabel itu pada kolom yang hanya memiliki sedikit nilai unik (misalnya itu mungkin kolom "jenis kelamin" yang hanya berisi "m" atau "f"), sebenarnya fts mungkin lebih baik daripada kasus itu karena anda ingin perlu mengambil blok pula untuk membangun set hasil. sekarang katakanlah meja anda adalah 99% "m" dan hanya 1% "f", kita harus fts dalam satu kasus atau menggunakan indeks dalam yang lain. tabel yang sama, permintaan yang sama, berpotensi empat paket permintaan yang berbeda tergantung pada isi tabel. hal-hal semacam ini adalah "statistik" dan bersifat individual untuk setiap basis data - bahkan dua basis data dengan tabel dan struktur indeks yang sama akan memiliki statistik yang berbeda.</p><p>singkatnya, dalam mesin basis data modern ada dua jenis optimisasi kueri: menulis ulang sql (optimasi berbasis aturan, seperti kompiler menulis ulang c anda agar lebih efisien) dan memilih jalur data yang tepat (optimasi berbasis biaya) , seperti kompiler jit yang mengidentifikasi hotspot saat runtime). anda hanya perlu khawatir tentang ini jika anda melihat pengoptimal kueri melakukan sesuatu yang jelas salah (mis. memilih fts saat anda <em>tah</em> indeks akan lebih baik).</p> <p>pertimbangkan untuk membuat tabel baru dengan skema yang sama ditambah kolom versi baris, dan tambahkan tampilan di atas kedua tabel yang melakukan gabungan semua. mintalah orang lain menggunakan tampilan, dan menulis alih-alih pemicu terhadap tabel &amp; tampilan yang mendasarinya.</p><p>sisipan harus dikirim ke tabel baru, pembaruan harus memindahkan data ke tabel baru, dan penghapusan harus diterapkan ke kedua tabel.</p><p>kemudian lakukan gerakan batch di latar belakang, pindahkan sebanyak mungkin catatan sekaligus ke tabel yang baru. anda masih dapat memiliki masalah konkurensi saat ini sedang berlangsung, dan beberapa rencana eksekusi craptacular, tetapi ini memungkinkan anda tetap online saat gerakan sedang berlangsung.</p><p>idealnya, anda memulai proses pada hari jumat sore untuk meminimalkan efek pada pengguna akhir, dan mencoba menyelesaikannya sebelum senin pagi. setelah siap, anda dapat mengubah tampilan menjadi hanya tabel baru, dan rencana eksekusi craptacular hilang. idealnya.</p><p>untuk menghindari pemicu pemicu saat data dimigrasi dalam kumpulan, lihat jumlah baris dalam tabel yang dihapus/dimasukkan dalam pemicu, dan lewati aktivitas jika mendekati jumlah baris dalam kumpulan anda.</p><hr><p>pada akhirnya, michael memutuskan untuk melewatkan tampilan (dan tidak menghapus dari tabel asli) untuk mendapatkan rencana yang lebih stabil. trade off pada dasarnya memegang dua salinan tabel. dia mengubahnya menjadi <a href="http://michaeljswart.com/2012/04/modifying-tables-online-part-1-migration-strategy/">serangkaian posting blog</a> .</p> <p><strong> tergantung pada fungsi generasi anda dan ukuran tabel akhir </strong></p><p>guid dimaksudkan untuk menjadi <em> unik secara global</em> pengidentifikasi. seperti yang dibahas dalam <a href="http://www.postgresql.org/docs/8.3/static/datatype-uuid.html">dokumentasi postgres 8.</a> tidak ada metodologi yang secara universal sesuai untuk menghasilkan pengidentifikasi ini, tetapi postgresql tidak mengirim dengan beberapa kandidat yang lebih berguna.</p><p>dari ruang lingkup masalah anda, dan kebutuhan untuk <em> offline</em> menulis, anda telah dengan rapi menghapus penggunaan apa pun selain guid, dan oleh karena itu tidak ada keuntungan kompensasi dari skema lain.</p><p>dari sudut pandang fungsional, panjang kunci biasanya tidak menjadi masalah pada segala jenis sistem modern, tergantung pada jumlah bacaan dan ukuran tabel. sebagai metodologi alternatif, klien offline dapat mengelompokkan catatan baru <em> tanpa</em> kunci utama dan cukup memasukkannya saat menghubungkan kembali. karena postgresql menawarkan tipe data "serial", klien tidak akan perlu menentukan id jika mereka dapat melakukan penulisan sederhana ke database.</p> <p>semuanya sesuai dengan standar ansi:</p><p> <a href="http://support.microsoft.com/kb/316626">trailing blank menjelaskan</a> :</p><blockquote> <p>sql server mengikuti spesifikasi ansi/iso sql-92 (bagian 8.2, aturan umum # 3) tentang cara membandingkan string dengan spasi. standar ansi membutuhkan padding untuk string karakter yang digunakan dalam perbandingan sehingga panjangnya cocok sebelum membandingkannya. padding secara langsung mempengaruhi semantik predikat klausa where dan having dan perbandingan string transact-sql lainnya. sebagai contoh, transact-sql menganggap string 'abc' dan 'abc' setara untuk sebagian besar operasi perbandingan.</p> <p>satu-satunya pengecualian untuk aturan ini adalah predikat like. ketika sisi kanan dari ekspresi predikat like fitur nilai dengan spasi tambahan, sql server tidak memadatkan dua nilai dengan panjang yang sama sebelum perbandingan terjadi. karena tujuan dari predikat like, menurut definisi, adalah untuk memfasilitasi pencarian pola daripada tes persamaan string sederhana, ini tidak melanggar bagian dari spesifikasi ansi sql-92 yang disebutkan sebelumnya.</p> </blockquote><p>berikut adalah contoh umum dari semua kasus yang disebutkan di atas:</p><pre><code>declare @a varchar(10) declare @b varchar(10) set @a = '1' set @b = '1 ' --with trailing blank select 1 where @a = @b and @a not like @b and @b like @a </code></pre><p>inilah beberapa detail lebih lanjut tentang <a href="http://msdn.microsoft.com/en-us/library/ms179859.aspx">mengeklik kosong dan klausa <code>like</code></a> .</p><p><strong> [~ # ~] tetapi [~ # ~] </strong> jika anda ingin membuatnya berbeda - anda dapat memutuskan untuk menggunakan fungsi <code>datalength</code> alih-alih <code>len</code>, karena</p><pre><code>select 1 where len('john ') = len('john') </code></pre><p>akan menempatkan anda 1 sebagai gantinya</p><pre><code>select 1 where datalength('john ') = datalength('john') </code></pre><p><strong> solusinya </strong> adalah</p><ul> <li>untuk menggunakan fungsi datalength untuk membedakan antara string</li> <li>untuk memasukkan string ke dalam tipe nvarchar - mungkin lebih baik menyatakan tipe ini ke parameter sp</li> </ul> <p>seperti yang saya jawab <a href="https://dba.stackexchange.com/questions/2820/why-dont-my-my-cnf-settings-work/2822#2822">di sini</a> , anda dapat menambahkan bagian untuk setiap pengguna/host/db yang anda hubungkan menggunakan sintaks di <code>~/.my.cnf</code> anda:</p><pre><code>[clienthost1] # note: client + host1 user=myuser password=mypass database=dbname host=server.location.com </code></pre><p>setelah ini ada di pengguna anda <code>.my.cnf</code>, anda dapat menggunakannya dengan melakukan ini pada baris perintah:</p><pre><code>$ mysql --defaults-group-suffix=host1 </code></pre> <p>saya tidak yakin di sini, tapi saya akan mengambil risiko. saya pikir masalah anda mungkin dengan catatan <code>deny control</code> anda. lihat <a href="http://msdn.microsoft.com/en-us/library/ms188338.aspx">di sini</a> sekitar setengah halaman:</p><p><strong> menolak izin kontrol pada database secara implisit menolak izin connect pada database. prinsipal yang ditolak izin kontrol pada database tidak akan dapat terhubung ke database itu. </strong></p><p>saya menyadari bahwa contoh adalah untuk basis data, tetapi gunakan satu tingkat lagi. a <code>deny control</code> di atas tabel akan menolak <em> semua</em> hak istimewa di atasnya, saya menduga. lakukan <code>revoke control</code> untuk menyingkirkannya dan lihat apakah itu memperbaiki masalah anda.</p><p>jika demikian, anda harus menempatkan pengguna dalam peran basis data atau menolak mereka hak istimewa eksplisit terhadap tabel.</p> <p>rencana yang anda miliki saat ini terlihat seperti rencana paling optimal bagi saya.</p><p>saya tidak setuju dengan pernyataan di jawaban lain bahwa ia mengirim baris 2.6m ke server jarak jauh.</p><p>rencana itu terlihat bagi saya seolah-olah untuk masing-masing 54 baris yang dikembalikan dari permintaan jarak jauh itu melakukan pencarian indeks ke tabel lokal anda untuk menentukan apakah cocok atau tidak. ini adalah rencana optimal.</p><p>mengganti dengan hash join atau gabung bergabung akan menjadi kontraproduktif mengingat ukuran tabel dan menambahkan perantara <code>#temp</code> table hanya menambahkan langkah tambahan yang sepertinya tidak memberi anda keuntungan.</p>