Arxiv

Archive for İ

Prepared Statement in MySQL

Mart 13, 2012 2 şərh

İlk öncə onu deyim ki, biz bu yazıda “city” table-ından istifadə edəcik. Link To Download City.sql. Sadəcə city.sql faylını run edin.
Yazının sonunda isə kiçik performance müqayisəsi var ki, ora da diqqət etsəniz əla olar 🙂
Prepared Statement SQL syntax-ı 3 SQL statement üzərində qurulub:
1. PREPARE — yaradır
2. EXECUTE — işlədir
3. DEALLOCATE PREPARE — silir
Misallarla izah edək. Aşağıdakı prepared statement-lər 2-si də tamamilə eynidir. Sadəcə yazılış fərqləri var.

mysql> PREPARE stmt1 FROM 'SELECT name, population FROM city WHERE countrycode=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a='AFG';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a;
+----------------+------------+
| name           | population |
+----------------+------------+
| Kabul          |    1780000 |
| Qandahar       |     237500 |
| Herat          |     186800 |
| Mazar-e-Sharif |     127800 |
+----------------+------------+
4 rows in set (0.00 sec)

İkinci növ yazılış tərzi isə:

mysql> SET @s='SELECT name, population FROM city WHERE countrycode=?';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @b='AFG';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt2 USING @b;
+----------------+------------+
| name           | population |
+----------------+------------+
| Kabul          |    1780000 |
| Qandahar       |     237500 |
| Herat          |     186800 |
| Mazar-e-Sharif |     127800 |
+----------------+------------+
4 rows in set (0.00 sec)

Prepared Statement-in yaradılmasına və istifadəsinə baxdıqdan sonra indi isə onun haqda bəzi maraqlı məlumatlar verək. Prepared Statement connection(session) spesifikdir. Yəni hər hansı bir user Prepared Statement yaradır və daha sonra disconnect olursa, həmin Prepared Statement avtomatik olaraq silinir. Gələn dəfə connect olanda yenisini yaratmaq məcburiyyətində qalacaq.
MySQL-də eyni zamanda (simultaneously) neçə Prepared Statement yaratmaq olar? Buna max_prepared_stmt_count system dəyişəni nəzarət edir.

mysql> select @@max_prepared_stmt_count;
+---------------------------+
| @@max_prepared_stmt_count |
+---------------------------+
|                     16382 |
+---------------------------+
1 row in set (0.00 sec)

Cavab 16382. Bu system dəyişənindən istifadə edərək biz həm də Prepared Statement-in istifadəsinin qarşısını ala bilərik. Bunun üçün sadəcə max_prepared_stmt_count=0 edək.

mysql> set global max_prepared_stmt_count=0;
Query OK, 0 rows affected (0.00 sec)

Və daha sonra Prepared Statement yaratmağa calışaq:

Mövcud olmayan Prepared Statement-dən istifadə etməyə çalışmaq ERROR-la nəticələnir:

Onu da qeyd edək ki, artıq mövcud olan eyni adlı Prepared Statement yaratmaq istərkən, əvvəlcə köhnəsi silinir daha sonra yerinə yenisi yaradılır. Məhz buna görədir ki, əgər yenisini yaradarkən hər hansı bir ERROR baş verirsə, köhnə Prepared Statement silinir lakin yerinə yenisi yaradılmır 🙂 Dəhşətdir elə deyil mi? 😛

mysql> SET @c='NLD';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt1 FROM 'SELECT ame, population FROM city WHERE countrycode=?';
ERROR 1054 (42S22): Unknown column 'ame' in 'field list'
mysql> EXECUTE stmt1 USING @c;
ERROR 1243 (HY000): Unknown prepared statement handler (stmt1) given to EXECUTE

Gördüyümüz kimi name yerinə ame yazılıb məhz bu səhv stmt1-in tamamilə silinməsinə gətirib çıxarıb. Gəlib çatdıq mənim fikrimcə ən maraqlı yerə. ƏSAS MƏSƏLƏ=PERFORMANCE 🙂

İlk öncə city-yə bənzər(eyni) vir table yaradaq:

CREATE TABLE city_log AS SELECT * FROM world.city;

Daha sonra isə Prepared Statement-imizi yaradaq:

PREPARE stmt FROM 'select * from city_log where population > ?';
SET @var=1;

Və işlədək:

EXECUTE stmt USING @var;

Query Profile-a baxaq:

Göstərici — 0.019668
İndi isə eyni şeyi adi select ilə edək:

SELECT * FROM city_log WHERE population>1;

Göstərici — 0.019888
Elə də böyük fərq hiss olunmadı amma ancaq lakin davam edək 🙂
Prepared Statement-i başqa cür işlədək:

SET @ var2=0;
EXECUTE stmt USING @var2;

Göstərici — 0.004675
Eyni şeyi select-lə etsək:

select * from city_log where population>0;

Göstərici — 0.019516
İndi isə biraz ümumiləşdirib yazaq.
Prepared Statement 1ci istifadəsi : 0.019668, 2ci istifadəsi : 0.004675
Select 1ci istifadəsi : 0.019888, 2ci istifadəsi : 0.019516
Fərq göz önündədir məncə 🙂

Kateqoriyalar: MySQL Etiketlər: ,

LOAD DATA INFILE

Fayldan məlumatı import etmək üçün LOAD DATA INFILE-dan istifadə olunur. Bəzi maraqlı xüsusiyyətlər var ki, onlar haqda danışmaq istərəm.
Deyək ki, out.txt faylı var və onun içində məlumatlar aşağıdakı şəkildə düzülmüşdür.

1,Shahriyar,1500
2,Orxan,2500
3,Axund,\N
4,Musfiq,\N

bu məlumatı “table1” cədvəlinə import etmək lazımdır.

CREATE TABLE table1(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10),
salary INT,
PRIMARY KEY(id)
);

Məhz bu məqsədlə:

LOAD DATA INFILE 'C:/Users/JRzayev/Desktop/out.txt'
INTO TABLE table1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';


FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\r\n’;—nə olduğunu bilmək üçün mənim əvvəlki yazıma təəccüblü nəzərlərlə bax! 😛 Əvvəlki Yazı
Və həmin cədvələ baxsaq, görərik ki həqiqətən də məlumatlar daxil edilmişdir.

Bəs əgər faylın əvvəlində bir neçə sətrdə əlavə dəxlisiz məlumatlar yerləşibsə onda necə? 🙂 Yəni Faylın içindəki məlumatlar aşağıdakı şəkildədir:

–Comment1
–Comment2
1,Shahriyar,1500
2,Orxan,2500
3,Axund,\N
4,Musfiq,\N

Və bu faylı düzgün şəkildə import edə bilmək üçün:

LOAD DATA INFILE 'C:/Users/JRzayev/Desktop/out.txt'
INTO TABLE table1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;


IGNORE 2 LINES—yuxarıdan ilk 2 sətri buraxmağı əmr edir.
Bəs deyək ki, bizə fayldan yalnız bəzi məlumatlar lazımdır hamısı yox.
Bunu göstərmək üçün table1-də dəyişiklik aparaq:

ALTER TABLE table1 DROP PRIMARY KEY;
ALTER TABLE table1 MODIFY id INT;

Cədvəlimizin son vəziyyəti:

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Faylımız isə bu şəkildədir:

1,Shahriyar,1500
2,Orxan,2500
3,Axund,\N
4,Musfiq,\N

Bizə burdan lazımdır ki,yalnız name və salary üçün məlumatlar seçilsin. Yəni 1,2,3,4(İD) ignore edilsin.

LOAD DATA INFILE 'C:/Users/JRzayev/Desktop/out.txt'
INTO TABLE table1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' (@id,name,salary);

Burda elə də dəhşətli bir şey yoxdur.Məsələ burasındadır ki, @id—user variable-ı heç yerdə istifadə olunmadığına görə sadəcə ignore edilir və cədvələ onun yerinə “default value” daxil edilir. İndiki halda bu “default value” bizdə NULL-dur. Və cədvəlimizə nəzər yetirsək bunu aydın görərik:

Daha ətraflı Documentasiyadan oxuya bilərsiz: LOAD DATA INFILE Syntax
Təşəkkürlər 🙂

Kateqoriyalar: MySQL Etiketlər: ,

SELECT … INTO OUTFILE

Hər hansı cədvəldən, fayla məlumatı export etmək üçün istifadə SELECT … INTO OUTFILE-dan istifadə olunur. Bu zaman bəzi maraqlı hadisələr baş verir ki, onu qeyd etmək lazımdı. Məhz bu məqsədlə dəhşətli table-ımızı yaradaq 🙂

CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10),
salary INT,
PRIMARY KEY(id)
);

Və İnsert verək:

INSERT INTO employee(name,salary) VALUES('Shahriyar',1500);

INSERT INTO employee(name,salary) VALUES('Orxan',2500);

INSERT INTO employee(name,salary) VALUES('Axund', NULL);

İndi isə hər şeyi default olaraq saxlayaraq export verək:

SELECT * FROM employee INTO OUTFILE 'C:/Users/JRzayev/Desktop/out.txt';

Desktop-da out.txt adında bir fayl yaranacaq.Faylı açdıqda:

Əgər diqqət yetirsəniz məlumatların necə səliqəsiz şəkildə yerləşdiyini görərsiz. “Göy” rənglə işarələnən \N, NULL-u göstərir. Yəni NULL məlumatlar export faylında \N kimi göstərilir.
Export faylımızı gözəlləşdirək. “Sarı” rənglə işarələnən yerlərdə yeni sətrə keçilsəydi daha gözəl olardı.

SELECT * FROM employee
INTO OUTFILE 'C:/Users/JRzayev/Desktop/out.txt'
LINES TERMINATED BY '\r\n';

Əvvəlki koda əlavə olan “LINES TERMINATED BY ‘\r\n’” line-ending-in necə olacağını bildirir. İndiki halda biz qeydləri yeni sətrə keçirdərək daha aydın hala gətiririk.

Davam edək:

SELECT *FROM employee
INTO OUTFILE 'C:/Users/JRzayev/Desktop/out.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

Əvvəlkinə əlavə olan “FIELDS TERMINATED BY ‘,’” field-lərin vergüllə aralı olacağını bildirir:

Davam edək:

SELECT *FROM employee
INTO OUTFILE 'C:/Users/JRzayev/Desktop/out.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Əvvəlkinə əlavə olan “ ENCLOSED BY ‘”‘ ” :

ENCLOSED BY-ın
OPTIONALLY ENCLOSED BY şəklində fərqli bir variantı var ki, izahı ingiliscə daha gözəldi:
The presence of OPTIONALLY causes output value quoting only for string columns, not for all columns.

SELECT * FROM employee
INTO OUTFILE 'C:/Users/JRzayev/Desktop/out.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Təşəkkürlər 🙂

Kateqoriyalar: MySQL Etiketlər: ,

View ilə invalid data handling

View və ondan istifadə səsəbləri,həmçinin view-dan necə istifadə etmək lazımdı ondan danışacıq.Daha sonra isə view vasitəsilə invalid data handling-ə baxacıq. Buna oxşar bir yazım var artıq SQL_MODE ilə invalid data handling
Aşağıdakı documentation-da View-dan istifadənin mümkün səbəbləri göstərilmişdir.Oxuyaraq davam edin…
Reasons to Use Views
Views provide several benefits compared to selecting data directly from base tables:

Access to data becomes simplified:

A view can be used to perform a calculation and display its result. For example, a view definition that invokes aggregate functions can be used to display a summary.

A view can be used to select a restricted set of rows by means of an appropriate WHERE clause, or to select only a subset of a table’s columns.

A view can be used for selecting data from multiple tables by using a join or union.
A view performs these operations automatically. Users need not specify the expression on which a calculation is based, the conditions that restrict rows in the WHERE clause, or the conditions used to match tables for a join.

Views can be used to display table contents differently for different users, so that each user sees only the data pertaining to that user’s activities. This improves security by hiding information from users that they should not be able to access or modify. It also reduces distraction because irrelevant columns are not displayed.

If you need to change the structure of your tables to accommodate certain applications, a view can preserve the appearance of the original table structure to minimize disruption to other applications. For example, if you split a table into two new tables, a view can be created with the name of the original table and defined to select data from the new tables such that the view appears to have the original table structure.

View özünü virtual cədvəl kimi aparır. Hər hansı table-a sanki dolayısı ilə çatırsan. Bir cədvəlin ya tam kopyasını ya da müəyyən bizə lazım olan hissəsini çıxardaraq view yarada bilərik.
Misallarla izah etməyə calışaq. Table yaradaq(hazır dump faylımdan götürmüşəm):

CREATE TABLE employees (
  emp_id int(11) NOT NULL AUTO_INCREMENT,
  Emp_Name varchar(30) DEFAULT NULL,
  address varchar(10) NOT NULL,
  salary varchar(10) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

Və insert edək:

INSERT INTO employees() VALUES (12,'Sales','0','2500'),(13,'IT','0','3500'),(14,'Admin','0','4500'),(15,'Admin','0','5500'),(16,'President','0','10500');


Bəli şəkildən də göründüyü kimi insert-imiz uğurludur və daha aşağıda da göründüyü kimi bizdə 4 column var.

İndi isə istəyirəm ki bu table üstündə bir view yaradım…Və bu view bütün column-lardan təşkil olunsun…

create view emp_view as select * from employees;

Və həqiqətən də employees table-ın bütün sütunlarından ibarət bir view yaradıldı

Select verdikdə isə görürük ki, employees cədvəlində olan bütün məlumatlar eynisi ilə emp_view-dədir:

İndi isə biraz dəyişikliklər etməklə müxtəlif view-lar yaradaq. employees cədvəlindən yalnız emp_id və emp_name-i götürək.

create view emp_view2 as select emp_id,emp_name from employees;


Indi isə salary-si 4500-dən çox olanları seçib view yaradaq:

alter table employees modify salary int not null;

create view emp_view3 as select * from employees where salary>'4500';


Bəli bizə lazım olan idi 🙂
Onu da qeyd edək ki, view vasitəsilə base table-ı (view yaradılarkən istifadə olunan cədvəl.indiki halda employees) UPDATE, DELETE və İNSERT yəni dəyişə bilərik. Bu haqda ətraflı yazmıram sadə bir şeydi əslində. Adi table-ı necə UPDATE, DELETE və İNSERT ediriksə view-nu da elə edirik.

*İnvalid Data Handling*
Bu Maraqlı xüsusiyyəti misal üzərində göstərməyə çalışaq. Bir yeni cədvəl yaradaq:

CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(15),
salary DOUBLE,
PRIMARY KEY(id)
);

Və VIEW yaradaq:

CREATE VIEW emp_view AS
SELECT *
FROM for_view
WHERE salary>2000 WITH CHECK OPTION;

Table boş olsa belə View yarada bilərik. İndi isə VIEW vasitəsilə insert verək:

INSERT INTO emp_view(name,salary) VALUES('Orxan',3000);

İndi isə bir başqa insert:

INSERT INTO emp_view(name,salary) VALUES('Esger',1500);

İndi isə izah edək. Deyək ki bizə lazımdı ki , hər hansı bir user işçilərin maaşlarını daxil edərkən 2000-dən aşağı maaşları (səhvən) daxil etməsin(çünki 2000-dən aşağı maaş alan yoxdu 😛 ). Və yaxud istənilən başqa şərt. Və əgər belə bir şey olsa bu rəqəm daxil edilməsin, əksinə error versin.

SQL Error (1369): CHECK OPTION failed 'test.emp_view'

Bunun səbəbi VIEW-nu yaradarkən WITH CHECK OPTION-nun əlavə edilməsidir.Təbii ki user input-u hər hansı kodla da yoxlamaq olar amma mənə elə gəlir ki, bu daha sadədir.
Və əgər yoxlasaq görərik ki, həm base table, həm də view dəyişilib və səhv məlumat həqiqətən də daxil edilməyib.

Təbii ki, istənilən VIEW ilə bunları etmək olmur.Yalnız UPDATABLE VIEW sayılan VIEW-lar.UPDATE,DELETE və İNSERT oluna bilən VIEW-lar UPDATABLE VIEW adlanırlar.
Hər hansı VIEW-nun UPDATABLE olub-olmadığını öyrənmək üçün:

SELECT * FROM information_schema.views WHERE table_name='emp_view' AND table_schema='test';

İS_UPDATABLE:YES — bəli bu VIEW UPDATABLE-dır.
VIEW metadata-nı həm də :

SHOW CREATE TABLE emp_view;

Və ya:

SHOW CREATE VIEW emp_view;

Daha ətraflı:
CREATE VIEW Syntax
Using Views
Updatable and Insertable Views
The INFORMATION_SCHEMA VIEWS Table

Kateqoriyalar: MySQL Etiketlər: , , ,