Kategori: MySQL

Bir projede kurulum yaparken tüm tabloları temizlemem gerekiyor. Tek tek elle yapsam olmaz, tablo adlarını bir diziye aktarıp truncate etsem saçma olur. Bunun için information_schema’ı kullanarak tabloları çektim ve döngüye sokarak temizleme işlemi yaptım. Yani sql sorgum şöyle;

SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema IN("db_adi", "db_adi2")

Burada concat ile değerleri birleştirince tüm tablolarıma ait otomatik truncate kodu çıkmış oldu. Şimdi bunu php ile birleştirdiğimizde;

<?php
// pdo db bağlantı işlemleri ..

$query = $db->query('SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN("mvc")')->fetchAll(PDO::FETCH_ASSOC);

foreach ($query as $row){
    echo $db->query(current($row))->fetch();
}

Not: Eğer SQLSTATE[42000]: Syntax error or access violation hatası alıyorsanız, foreach’den önce yani sorguları çalıştırmadan önce FOREIGN_KEY_CHECKS değerini 0’a eşitleyip deneyin. Yani;

<?php
// pdo db bağlantı işlemleri ..

$query = $db->query('SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN("mvc")')->fetchAll(PDO::FETCH_ASSOC);
$db->query('SET FOREIGN_KEY_CHECKS=0')->fetch();
foreach ($query as $row){
    echo $db->query(current($row))->fetch();
}

İşte bu kadar ???? Benim hazırladığım BasicDB sınıfında ise son versiyonunu indirdikten sonra şu şekilde kullanmak yeterli;

<?php
// basicdb bağlantı işlemleri ..
$db->truncateAll();

Bu metod içerisinde AUTO_INCREMENT değerleride 1’den başlayacak şekilde ayarlanıyor. Dilerseniz kendi şu sql sorgusu ile tablolarınızın başlangıç değerlerini de ayarlayabilirsiniz.

ALTER TABLE `db_adi`.`tablo_adi` AUTO_INCREMENT = 1; # 1 ya da kaçtan başlayacaksa o

Bu yazımda sizlere mysql’de json olarak depoladığımız veriler üzerinde listeleme, arama, düzenleme gibi işlemler nasıl yapılır bunlardan bahsetmek istiyorum. Eğer JSON ile uğraşıyorken aklınıza NoSQL veritabanları geliyorsa haklısınız, ancak mysql’de de bir takım işlemler yapabildiğimizi unutmamak lazım ????

Öncelikle neden JSON olarak veri depolarız? Ben kendi adıma konuşacak olursam, örneğin seo bilgilerini json formatında tutuyorum. Yani title, description, keywords ama yarın öbürgün ekstra bir bilgi daha ekleyebilirim örneğin noindex, canonical vb. Bunun için sürekli gidip kolon oluşturmak tamamen saçmalık olurdu. Bu yüzden tüm bu değerleri tek bir kolon altında json formatında tutuyorum. Ve yeri geldiğinde bunları filtrelemek, bunlar içinde arama yapmak gerekirse değiştirmek, listelerken ayrı ayrı göstermek isteyebilirim.

İşte bu noktada mysql’de bazı fonksiyonlar bize yardım edecek. Alışılmışın dışında bir sorgu yazıyormuşuz gibi görünsede, aslında ne kadar kolay olduklarını birazdan anlayacaksınız ????

JSON Veri Eklemek

Biz aslında PHP tarafında json_encode() fonksiyonu ile dizileri ya da objeleri json haline getirerek ekleyebiliyoruz. Örnek vermek gerekirse;

<?php

$arr = [
    'title' => 'Erbilen.net',
    'description' => 'Tayfun Erbilen\'in Bloğu'
];

echo json_encode($arr);
// Çıktı: {"title": "Erbilen.net", "description": "Tayfun Erbilen'in Bloğu"}

PHP’de json ile ilgili işlemlerin daha fazlası için şu yazıma gözatabilirsiniz. MySQL tarafında elle json veri eklemek istersek;

INSERT INTO tablo_adi
SET kolon_adi = '{"title": "Erbilen.net", "description": "Tayfun Erbilen\'in Bloğu"}'

Gördüğünüz gibi çok ekstra bir şeyi yok. JSON ekleme aynı zamanda şu fonksiyonlar ile de olabilir;

JSON_ARRAY

Bu fonksiyon geriye array döndürecektir. Kullanımı ise;

INSERT INTO tablo_adi
SET kolon_adi = JSON_ARRAY('tayfun','erbilen');
#    ['tayfun','erbilen']

JSON_OBJECT

Bu fonksiyon geriye obje döndürecektir. Kullanımı ise;

INSERT INTO tablo_adi
SET kolon_adi = JSON_OBJECT('ad','erbilen','soyad','erbilen');
#    {"ad": "erbilen", "soyad": "erbilen"}<span id="mce_marker" data-mce-type="bookmark" data-mce-fragment="1">​</span>

JSON_MERGE

Bu fonksiyon array ve objeleri birleştirecektir. Kullanımı ise;

INSERT INTO tablo_adi
SET kolon_adi = JSON_MERGE('{"ad":"tayfun"}','{"soyad":"erbilen"}', '[1,2,3]');
#   [{"ad": "tayfun", "soyad": "erbilen"}, 1, 2, 3]

JSON_TYPE ve JSON_VALID

Bu iki fonksiyon json formatının doğruluğunu kontrol eder. JSON_TYPE’da geriye ARRAY, OBJECT ya da hata mesajı döner. JSON_VALID’de ise format doğru ise 1 değil ise 0 döner. Kullanımı ise;

-- dönen değer ARRAY:
SELECT JSON_TYPE('[5, 7, "erbilen.net"]');

-- dönen değer OBJECT:
SELECT JSON_TYPE('{"ad": "tayfun", "soyad": "erbilen"}');

-- dönen değer hata mesajı:
SELECT JSON_TYPE('{"ad": "tayfun", "soyad": "erbilen"');

-- dönen değer 1:
SELECT JSON_VALID('[5, 7, "erbilen.net"]');

-- dönen değer 1:
SELECT JSON_VALID('{"ad": "tayfun", "soyad": "erbilen"}');

-- dönen değer 0:
SELECT JSON_VALID('{"ad": "tayfun", "soyad": "erbilen"');

JSON içinde Arama

JSON verileri depoladıktan sonra önemli olan onlar içerisinde belli bir düzende arama yapıp onları listelemektir. Bunun için birkaç yararlı fonksiyonu inceleyeceğiz;

JSON_CONTAINS()

Bu fonksiyon ile JSON içerisinde istediğimiz şeyin olup olmadığını kontrol ediyoruz. Örneğin array’de bir değer ya da obje’de bir key value gibi. Hemen örneklendirelim;

# kolon_adi = {"ad":"tayfun","soyad":"erbilen"}
SELECT * FROM tablo_adi
WHERE JSON_CONTAINS(kolon_adi, '{"soyad": "erbilen"}');

Yukarıdaki örnekte kolon_adi kolonunda depolanan json değerler içerisinde soyad objesi olup değeri erbilen’e eşit olanlar listelenecektir.

SELECT * FROM tablo_adi
WHERE JSON_CONTAINS(kolon_adi, '[2]');

Yukarıdaki örnekte ise kolon_adi kolonunda depolanan array içerisinde 2 değeri olanlar listelenecektir.

JSON_SEARCH()

Bu fonksiyon ile JSON olarak depoladığımız değerlerin path’leri bulmanızı sağlıyor. İlk parametresi JSON değeri, ikinci parametresi ise all ya da one olarak belirlenmeli. all derseniz eğer aradığınız değer birden fazla ise hepsini döndürür one derseniz sadece ilkini döndürür. 3. parametre ise aradığınız değer olarak belirlenmeli. Örneğin;

SELECT JSON_SEARCH(kolon_adi, 'all', 'soyad') FROM tablo_adi
# Örnek çıktı:   ["$[0]", "$[1].deneme"]

JSON_EXTRACT()

Bu fonksiyon ile json pathlerini belirleyerek işlem yapabiliyoruz. Örneğin json veri içerisinde soyad’a sahip olanları listelemek istersek;

SELECT * FROM test_tablo
WHERE JSON_EXTRACT(test_kolon, '$.soyad')

Bu örnekte test_kolon içerisinde aşağıdaki gibi bir json saklandığını farz ediyoruz.

{"ad": "tyfn", "soyad": "erbilen"}

Kısaca aşağıdaki JSON verisini baz alırsak;

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

O halde JSON_EXTRACT() içerisinde path’leri şu mantıkta kullanabiliriz;

$.a – 1 döndürür
$.c – [3,4] döndürür
$.c[0] – 3 döndürür
$.d.e – 5 döndürür
$**.e – 5 döndürür

Sorgularda JSON Yollarını Almak

JSON Path’leri herhangi bir fonksiyon yardımı olmadan da sorgularda kullanabiliriz. Bunun için tablo_adi->’$.path’ şeklinde bir kullanım söz konusu. Yukarıdaki kullanım şekillerini baz alabilirsiniz.

SELECT kolon_adi->'$.ad' as ad FROM tablo_adi;
# ya da
SELECT * FROM tabo_adi
WHERE kolon_adi->'$.ad' = 'tayfun';

JSON Verilerini Düzenleme

Bu işlem için birkaç fonksiyon var.

JSON_REPLACE()

İlk parametresi json verisi, 2. parametresi path yani yolu, 3. parametresi ise yeni değeri. Daha sonrasında path, value, path, value şeklinde devam ederek kullanılabilir.

UPDATE tablo_adi
SET kolon_adi = JSON_REPLACE(kolon_adi, '$.uye.ad', 'Tayfun')

JSON_SET()

JSON_REPLACE ile aynıdır. Kullanımlarıda aynı şekildedir.

JSON_INSERT()

JSON verisine yeni değerler eklemek için kullanılıyor. Örneğin;

UPDATE tablo_adi
SET kolon_adi = JSON_INSERT(kolon_adi, '$.adsoyad', 'Tayfun Erbilen')
WHERE id = 2

JSON_REMOVE()

JSON verisinden değer silmek için kullanılıyor. Örneğin;

UPDATE tablo_adi
SET kolon_adi = JSON_REMOVE(kolon_adi, '$.adsoyad')
WHERE id = 2

Bu makalemizde buraya kadardı ????

Eğer daha fazla merak ettiyseniz mysql’in kendi döküman sayfasından devamını inceleyebilirsiniz.

Bkz: https://dev.mysql.com/doc/refman/en/json-functions.html