MariaDB INSERT, UPDATE ve DELETE: ON DUPLICATE KEY UPDATE, REPLACE ve RETURNING

INSERT: satır eklemenin dört yolu

INSERT üç sözdizimi biçimine sahiptir: VALUES listesi, SET sutun = deger biçimi ve başka bir sorgunun sonucunu ekleyen INSERT ... SELECT. Sütun listesi isteğe bağlıdır, ama vermezseniz her sütun için tablo tanımındaki sırayla bir değer yazmanız gerekir. Belge ve kılavuz bu yüzden sütunları açıkça listelemeyi iyi alışkanlık olarak gösterir: böylece sıralamaya bağımlı kalmaz, varsayılan değeri olan sütunları atlayabilirsiniz.

INSERT INTO person (first_name, last_name) VALUES ('John', 'Doe');

INSERT INTO tbl_name VALUES (1, "row 1"), (2, "row 2");

INSERT INTO person SET first_name = 'John', last_name = 'Doe';

INSERT INTO contractor SELECT * FROM person WHERE status = 'c';

• VALUES ve VALUE anahtar sözcükleri, kaç satır eklerseniz ekleyin birbirinin yerine kullanılabilir.
• Birden çok satırı tek ifadede, parantezleri virgülle ayırarak eklemek belgeye göre bir hız optimizasyonudur.
• Listede olmayan bir AUTO_INCREMENT sütunu otomatik değer alır; diğer atlanan sütunlara varsayılan değerleri, varsayılan yoksa izin veriliyorsa NULL yazılır.
• Belirli bir sütuna açıkça varsayılan değer vermek için değer listesinde DEFAULT sözcüğünü kullanabilirsiniz.
• VALUES listesindeki bir alt sorgu, eklenen tabloyu okuyabilir; sonucu ekleme yapılmadan önce hesaplanır.

INSERT ... SELECT ile hedef sütun listesinin, SELECT listesindeki sütunlarla sayı ve genel tip uyumu bakımından eşleşmesi gerekir. Kılavuzdaki örnek başka bir veritabanındaki tablodan seçim yapar; tablo adını veritabani.tablo biçiminde yazabildiğiniz için veritabanları arasında satır kopyalamak mümkündür:

INSERT INTO softball_team (last_name, first_name, telephone)
  SELECT name_last, name_first, tel_home
  FROM company_database.employees
  WHERE is_on_softball_team = 'Y';

Kılavuz, INSERT ... SELECT ifadesinin aynı tabloyu doğrudan hem hedef hem kaynak olarak kullanmasının genellikle geçici tablo gibi ara yöntemler gerektirdiğini de not eder.

LOW_PRIORITY ve DELAYED ne zaman anlamlı?

LOW_PRIORITY, eklemeyi tabloyu okuyan başka istemci kalmayana kadar bekletir; HIGH_PRIORITY ise ifadeye SELECT ile aynı önceliği verir. Belgeye göre bu sözcükler yalnızca tablo düzeyinde kilit kullanan MyISAM, MEMORY ve MERGE gibi motorlarda etkilidir; varsayılan InnoDB için bir şey değiştirmez. INSERT DELAYED ise isteği kuyruğa alıp istemciye hemen dönüş yapar. Kılavuz bunun yalnızca işlem desteklemeyen motorlarla çalıştığını, başka bir motorda hata verdiğini, satırlar yazılmadan önce sunucu durursa bellekte bekleyen satırların kaybolacağını ve başarı onayı alınamayacağını belirtir.

INSERT IGNORE: hatayı uyarıya çevirmek

Normalde INSERT bir hatayla karşılaşınca durur. IGNORE eklendiğinde belgeye göre tüm hatalar uyarıya dönüşür ve diğer satırların eklenmesi sürer. Çakışan anahtar değerleri atlanır, geçersiz değerler ise en yakın geçerli değere çevrilip uyarıyla eklenir. Belgedeki örnek:

CREATE TABLE t1 (x INT UNIQUE);

INSERT INTO t1 VALUES(1),(2);

INSERT INTO t1 VALUES(2),(3);
ERROR 1062 (23000): Duplicate entry '2' for key 'x'
SELECT * FROM t1;
+------+
| x    |
+------+
|    1 |
|    2 |
+------+

INSERT IGNORE INTO t1 VALUES(2),(3);
Query OK, 1 row affected, 1 warning (0.04 sec)

SHOW WARNINGS;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1062 | Duplicate entry '2' for key 'x' |
+---------+------+---------------------------------+

İlk deneme 1062 hatasıyla tümüyle reddedilir; IGNORE ile 2 atlanır, 3 eklenir. Dikkat edilecek nokta, IGNORE'un yalnızca tekrar eden anahtarları değil, aralık dışı veya dönüştürülemeyen değerleri de sessizce düzeltmesidir. Yalnızca çakışmaları yönetmek istiyorsanız aşağıdaki yöntemler daha kontrollüdür.

INSERT ... ON DUPLICATE KEY UPDATE (upsert)

Bu ifade, eklenecek satır bir birincil veya benzersiz anahtarla çakışırsa ekleme yerine var olan satırda UPDATE yapar. Etkilenen satır sayısı yeni satır eklendiyse 1, güncelleme yapıldıysa 2 olarak raporlanır (istemci API'sinde CLIENT_FOUND_ROWS bayrağı ayarlı değilse). Belgedeki örnek:

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

INSERT INTO ins_duplicate VALUES (1,'Antelope') 
  ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)

Çok satırlı eklemelerde, UPDATE kısmında eklenmek istenen değere başvurmak için VALUE(sutun) fonksiyonu kullanılır. Belgeye göre fonksiyonun adı standart tablo değer yapıcılarıyla çakışmaması için VALUE() olarak değiştirilmiştir; eski VALUES() yazımı yalnızca bu ifadenin içinde hâlâ çalışır:

INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);

• Birden çok benzersiz indeks eşleşirse yalnızca ilk eşleşen satır güncellenir. Belge, birden fazla benzersiz indeksi olan tablolarda bu ifadenin kullanılmasını önermez.
• Yeni satır eklendiğinde LAST_INSERT_ID() o satırın AUTO_INCREMENT değerini döndürür; güncelleme olduğunda etkilenmez.
• İfade hem INSERT hem UPDATE tetikleyicilerini çalıştırır. Tetikleyiciler trigger yazısında anlatılıyor.
• DELAYED seçeneği bu ifadeyle kullanıldığında yok sayılır.

REPLACE: sil ve yeniden ekle

REPLACE, INSERT gibi çalışır; fark, yeni satır bir birincil ya da benzersiz anahtarda eski bir satırla çakışırsa eski satırın önce silinmesidir. Birden fazla benzersiz anahtar varsa yeni satır birden fazla eski satırla çakışabilir ve bu durumda çakışan tüm satırlar silinir. Tabloda böyle bir anahtar yoksa REPLACE düz INSERT ile aynıdır. Belgenin uyardığı yan etkiler önemlidir:

• AUTO_INCREMENT alanı yeni bir değer alır, eski numara kaybolur.
• Yabancı anahtarlar varsa ON DELETE eylemi tetiklenir; CASCADE tanımlıysa bağlı satırlar da silinebilir.
• DELETE ve INSERT tetikleyicileri çalışır.
• İfadede verilmeyen sütunlar eski satırdan alınmaz, varsayılan değerlerine döner. SET col = col + 1 gibi bir atama DEFAULT(col) + 1 olarak yorumlanır.
• Kullanmak için tabloda hem INSERT hem DELETE yetkisi gerekir.

Belge bu davranışlardan kaçınmak için INSERT ... ON DUPLICATE KEY UPDATE kullanmayı önerir. Üç yöntemi yan yana koyunca fark netleşir:

RETURNING ile etkilenen satırları almak

INSERT ... RETURNING eklenen satırların istediğiniz sütunlarını ya da bunlardan hesaplanan ifadeleri sonuç kümesi olarak döndürür. Böylece otomatik üretilen değerleri ayrı bir SELECT yapmadan okuyabilirsiniz. Belgedeki örnekte t sütunu, ilk TIMESTAMP sütunu olarak otomatik doldurulmuştur:

CREATE OR REPLACE TABLE t2 (id INT, animal VARCHAR(20), t TIMESTAMP);

INSERT INTO t2 (id) VALUES (2),(3) RETURNING id,t;
+------+---------------------+
| id   | t                   |
+------+---------------------+
|    2 | 2021-04-28 00:59:32 |
|    3 | 2021-04-28 00:59:32 |
+------+---------------------+

RETURNING içinde fonksiyonlar, operatörler ve saklı fonksiyonlar kullanılabilir, ancak toplama fonksiyonları kullanılamaz. Birden çok satır veya sütun döndüren alt sorgulara da izin verilmez. REPLACE ... RETURNING ve tek tablolu DELETE ... RETURNING de desteklenir. UPDATE ... RETURNING ise belgeye göre MariaDB 13.0 ile gelmiştir; 13.x serisi önizleme olduğundan MariaDB 12.3 LTS üzerinde kullanmayı planlamayın.

UPDATE: var olan satırları değiştirmek

UPDATE, SET ile hangi sütunlara hangi değerin yazılacağını, WHERE ile hangi satırların etkileneceğini belirtir. WHERE yoksa tablodaki bütün satırlar güncellenir; kılavuz bunu kalın harflerle vurgular. Değer olarak bir ifade veya sütunu varsayılanına döndürmek için DEFAULT verebilirsiniz.

UPDATE table3
SET col1 = 'new_value_a', col2 = 'new_value_b'
WHERE id_column < 100;

Tek tablolu güncellemede atamalar soldan sağa değerlendirilir; yani SET a = a + 1, b = a yazarsanız b artırılmış değeri alır. SIMULTANEOUS_ASSIGNMENT SQL kipi açıksa tüm atamalar aynı anda değerlendirilir. Çok tablolu güncellemede sıra garanti edilmez. Tek tablolu biçimde ORDER BY ve LIMIT ile hangi satırların hangi sırayla güncelleneceğini sınırlayabilirsiniz; belge, LIMIT'li UPDATE'in replikasyon için güvenli olmadığını, LIMIT 0'ın bunun istisnası olduğunu not eder.

Güncellenen tablo, WHERE içindeki alt sorguda kaynak olarak da kullanılabilir. Belgedeki örnek en büyük c2 değerine sahip satırı artırır:

CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (10,10), (20,20);

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);

SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|   10 |   10 |
|   21 |   20 |
+------+------+

Başka bir tablodaki değerlere göre güncellemek için çok tablolu sözdizimi kullanılır. MariaDB 12.3'ten itibaren UPDATE ifadesinin başına bir WITH bloğu (CTE) da yazılabilir; belgeye göre CTE'ler burada salt okunurdur ve yalnızca CTE dışındaki tablo güncellenir. CTE sözdizimi CTE ve WITH RECURSIVE yazısında.

UPDATE products p
JOIN stock_levels s ON p.product_id = s.product_id
SET p.stock_count = s.current_stock
WHERE s.warehouse_id = 'WHA';

IGNORE ile kullanılan UPDATE hata olsa da durmaz: anahtar çakışması yaratacak satırlar güncellenmez, dönüştürme hatası verecek değerler en yakın geçerli değere çekilir.

DELETE: satır silmek

DELETE FROM tablo WHERE kosul koşulu sağlayan satırları siler ve silinen satır sayısını döndürür; bu sayı ROW_COUNT() ile de okunabilir. WHERE yoksa tüm satırlar silinir. Tek tablolu biçimde ORDER BY ve LIMIT desteklenir, bu da büyük tabloları parça parça temizlemek için kullanışlıdır:

DELETE FROM page_hit ORDER BY TIMESTAMP LIMIT 1000000;

Belgede sürüm notlarıyla birlikte verilen yenilikler şunlardır: tek tablolu DELETE içinde takma ad (DELETE FROM t1 AS a1 ...) MariaDB 11.6'dan, çok tablolu DELETE ile ORDER BY ve LIMIT kullanımı ve tek tablolu DELETE içinde indeks ipuçları 11.8.1'den, WITH ile başlayan CTE sözdizimi 12.3'ten itibaren kullanılabilir. Bir tablodan, başka bir tabloyla eşleşen satırları silmek için birleştirme sözdizimi kullanılır; aşağıdaki örnekte yalnızca post tablosundan satır silinir:

DELETE post FROM blog INNER JOIN post WHERE blog.id = post.blog_id;

DELETE ... RETURNING silinen satırları döndürür; yalnızca tek tablolu biçimde çalışır ve toplama fonksiyonu içeremez. Belgedeki çıktı:

DELETE FROM t RETURNING f1;
+------+
| f1   |
+------+
|    5 |
|   50 |
|  500 |
+------+

Tablonun tamamını boşaltacaksanız ve silinen satır sayısına ihtiyacınız yoksa belge daha hızlı olan TRUNCATE TABLE'ı önerir. Ancak bir transaction içindeyken veya tabloda kilit tutarken TRUNCATE TABLE kullanılamaz, DELETE kullanılabilir.

Uygulamada güvenli değişiklik akışı

Yanlış bir WHERE ile yapılan toplu güncelleme veya silme, en sık karşılaşılan veri kayıplarından biridir. Belgelerden çıkan pratik bir akış şöyle özetlenebilir: önce aynı koşulu bir SELECT ile çalıştırıp hangi satırların etkileneceğini görün; ardından InnoDB tablosunda değişikliği bir transaction içinde yapın, sonucu kontrol edip COMMIT veya ROLLBACK ile bitirin. Transaction kullanımı transaction ve izolasyon seviyeleri yazısında anlatılıyor.

SELECT id, status FROM orders WHERE status = 'pending' AND created < '2025-01-01';

START TRANSACTION;
UPDATE orders SET status = 'expired' WHERE status = 'pending' AND created < '2025-01-01';
SELECT ROW_COUNT();
COMMIT;

Uygulama kodundan bu ifadeleri gönderirken kullanıcıdan gelen değerleri doğrudan SQL metnine eklemeyin; parametreli sorgu kullanın. Konu SQL injection önleme ve PHP PDO ile veritabanı bağlantısı yazılarında ele alınıyor.

Sık yapılan hatalar

• UPDATE veya DELETE'i WHERE olmadan çalıştırmak. Tablodaki tüm satırlar etkilenir; önce aynı koşulla SELECT yapın.
• REPLACE'i güvenli bir upsert sanmak. Eski satır silindiği için verilmeyen sütunlar varsayılana döner, numara değişir ve ON DELETE CASCADE bağlı kayıtları silebilir.
• INSERT IGNORE ile tüm hataları gizlemek. Aralık dışı değerler de kırpılarak eklenir; uyarıları SHOW WARNINGS ile kontrol edin.
• Birden çok benzersiz indeksi olan tabloda ON DUPLICATE KEY UPDATE kullanmak. Yalnızca ilk eşleşen satır güncellenir; belge bunu önermez.
• Sütun listesi vermeden INSERT yazmak. Tabloya sütun eklendiğinde ifade bozulur veya değerler yanlış sütunlara gider.
• 12.3 üzerinde UPDATE ... RETURNING denemek. Bu özellik 13.0 ile gelmiştir; güncellenen değeri ayrıca SELECT ile okuyun.

Sık Sorulan Sorular

Eklediğim satırın otomatik numarasını nasıl öğrenirim?

Aynı oturumda SELECT LAST_INSERT_ID(); çalıştırabilir ya da ekleme sırasında INSERT ... RETURNING id kullanabilirsiniz. Ayrıntı veri tipleri yazısının AUTO_INCREMENT bölümünde.

INSERT INTO yazmak zorunlu mu, INSERT yetmez mi?

Kılavuza göre INTO sözcüğü isteğe bağlıdır; okunabilirlik için yaygın olarak yazılır. INSERT table1 VALUES(...) da geçerlidir.

Çok sayıda satırı hızlı eklemenin yolu nedir?

Birden çok satırı tek INSERT ile göndermek belgede hız optimizasyonu olarak anılır. Dosyadan büyük veri yüklemek için ise LOAD DATA INFILE ve mariadb-import daha uygundur.

Silinen satırları geri alabilir miyim?

InnoDB tablosunda değişikliği bir transaction içinde yaptıysanız ve henüz commit etmediyseniz ROLLBACK ile geri dönebilirsiniz. Commit edilmiş silmeler için yedek gerekir; noktasal geri dönüş için binary log ve PITR yazısına bakın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · SELECT, WHERE, ORDER BY ve LIMIT · transaction · trigger · LOAD DATA · SQL injection önleme