Arxiv

Posts Tagged ‘sql_mode’

SQL_MODE ilə invalid data handling

İndiki mövzumuz data tiplərin minimum və maximum value və.s , həmçinin data type conversion dediyimiz bir şey haqqındadır. Söhbətimiz İNT və Varchar tipləri üzərində gedəcək.

Söhbətimizi əyani olaraq davam etdirək. Table yaradaq:

CREATE TABLE students (
    Stud_ID int(3) NOT NULL,
    Stud_Name VARCHAR(25) NOT NULL,
    Stud_Phone VARCHAR(5) NOT NULL,
    Stud_Address VARCHAR(50)
);

Qeyd edək ki, biz non-strict sql mode-unda işləyirik.
(MySQL-də SQL_MODE əhəmiyyəti)

set sql_mode='';

Daha sonra da insert-lər verək:

mysql> insert into students() values(1,'Orxan','0554962101','Yeni Yasamal');
Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'Stud_Phone' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

Gördüyümüz kimi, varchar(5) olan sütuna uzunluğu 10 olan bir məlumat daxil etdikdə o 5-ə truncate olunur.
select

Davam edək indi int-ə(stud_id) daha uzun bir məlumat daxil edək:

mysql> insert into students() values(444444,'Elvin','05567','Sovetski');
Query OK, 1 row affected (0.11 sec)

Nə error nə də ki bir Warning var və məlumat öz qaydasında daxil edilir:
select2

Demək ki, int(3) yazılsa da 3-də daha uzun bir rəqəm ora daxil edilə bilərmiş.
indi isə int-ə hər hansı bir string, varchar-a isə hər hansı bir rəqəm daxil edək:

mysql> insert into students() values('Mahmud',12345,'05078','Iceriseher');
Query OK, 1 row affected, 1 warning (0.11 sec)

stud_id int-dir ora ‘Mahmud’,stud_name varchar-dır ora 12345 daxil edirik.və nəticədə Warning görürük.
warnings

insert

Yuxarıdan aydın olur ki,non-strict sql_mode-da, int tipli column-a string insert etmək istədikdə MySQL həmin string-in yerinə 0 daxil edir!. Amma varchar tipli column-a integer daxil etdikdə onu olduğu kimi daxil edir. Demək ki, kimsə səhv edib tipləri qarışdırsa ağzına gələni daxil edəcək 🙂 bir daha deyirəm bu non-strict sql_mode-da belədir

İndi isə column definition-larda data type-ların özlərini dəyişək. Yəni table içində düzgün məlumatlar ola-ola int->varchar, varchar->int conversion edək. Təsəvvür edək ki, aşağıdakı məlumatlar var:

mysql> select * from students;
+---------+-----------+------------+--------------+
| Stud_ID | Stud_Name | Stud_Phone | Stud_Address |
+---------+-----------+------------+--------------+
|       1 | Orxan     | 05549      | Yeni Yasamal |
|  444444 | Elvin     | 05567      | Sovetski     |
|       2 | Mahmud    | 05078      | Iceriseher   |
+---------+-----------+------------+--------------+
3 rows in set (0.00 sec)

stud_id-nin data tipini varchar edək:

mysql> alter table students modify stud_id varchar(3) not null;
Query OK, 3 rows affected, 1 warning (0.32 sec)
Records: 3  Duplicates: 0  Warnings: 1

warning-ə baxaq və select verək:
select5

Yaxşı bayaqdan int-ə string gedir, data truncate olur və.s…Aləm dəyib bir-birinə 🙂 Bunun qarşısını alacaq bir şey yoxdur mu? Yəni ən azından əgər data truncate olacaqsa bir ERROR çıxsın ortaya…Məsələn, yuxarıdakı kimi int-ə string insert etmək istədikdə 0 daxil etməsin onun əvəzinə ERROR versin ki, işimizi bilək…
Adətən bu cür check-ləri PHP-də edirlər mən bilən. Data İnput Handling(error handling) deyilir səhv etmirəmsə buna. Mən bütün PHP programmer-ləri bu işi MySQL-in üstünə atmağa dəvət edirəm. 🙂
Cox sadə yolu var…STRİCT sql_mode. Bayaqkı bütün nümunələrimiz non-strict sql_mode-da çalışmışdı indi isə STRİCT-də işlədək. Yəni sql_mode-u dəyişək:

mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

Və indi də bayaqdan yazdıqlarımızı bir daha yazmağa çalışaq…
varchar(5) olan sütuna uzunluğu 10 olan bir məlumat daxil etdikdə:

mysql> insert into students() values(1,'Orxan','0554962101','Yeni Yasamal');
ERROR 1406 (22001): Data too long for column 'Stud_Phone' at row 1

Bəh bəh nə gözəl bir ERROR…

Daha sonra , int-ə hər hansı bir string, varchar-a isə hər hansı bir rəqəm daxil etdikdə:

mysql> insert into students() values('Mahmud',12345,'05078','Iceriseher');
ERROR 1366 (HY000): Incorrect integer value: 'Mahmud' for column 'stud_id' at r

Yenə bəh bəh… 🙂

Gördüyümüz kimi strict sql_mode-u enable etməklə biz təminat alırıq ki, int tipli column-da yalnız integer olacaq və həmçinin də hər hansı varchar(5) olan column-a yalnız 5 simvollu string daxil ediləcək…Əgər belə olmazsa ERROR alacıq.
Hər kəsi sql_mode-a daha çox fikir verməyə çağırıram, çünki bir çoxu işləri yüngülləşdirir.

SQL_MODE haqqında daha ətraflı :
MySQL-də SQL_MODE əhəmiyyəti

Təşəkkürlər…

Advertisements

MySQL-də SQL_MODE əhəmiyyəti

MySQL-in bir çox xarakteristikalarını SQL_MODE vasitəsilə dəyişmək mümkündür. Bu xüsusən database üçün düzgün input qaydalarını müəyyən etmək üçün əhəmiyyətlidir.
Məsələn default olaraq MySQL “division by zero” (0-a bölmə) olduqda NULL daxil edir. Lakin təbii ki , bizə bu zaman ERROR verməsi daha çox sərf edər.
Və.s bu kimi xüsusiyyətləri SQL_MODE ilə idarə etmək olar.
İlk öncə default SQL_MODE-un nə olduğunu öyrənək:

select @@sql_mode;

default sql_mode

CREATE TABLE t2 (
id INT(11),
name VARCHAR(4)
);

Və insert etdikdə STRICT_TRANS_TABLES-ın əməlləri olaraq ERROR alırıq.Çox sağolsun 🙂
Bu mode aktiv edildikdə əmin ola bilərik ki, “out of range” məlumat daxil edilməyəcək.

mysql> insert into t2 values(15,'Besiktas');
ERROR 1406 (22001): Data too long for column 'name' at row 1

NO_AUTO_CREATE_USER—bu da çox əhəmiyyətli bir mode-dur.
Deyək ki siz bilmədən mövcud olmayan bir user-ə GRANT verirsiz. Əgər bu mode aktiv deyilsə, GRANT icazəsiz olaraq yeni bir user yaradacaq. Bu mode aktiv olsa, ERROR çıxar və bunun qarşısını alacaq.
Göstərmək məqsədilə SQL_MODE-u dəyişək.

set @@global.sql_mode=ANSI;

Reconnect olaq və

select @@sql_mode;

Gördüyümüz kimi NO_AUTO_CREATE_USER mode-u burda yoxdur. Bu səbəbdəndir ki,

GRANT SELECT ON tr_blog.military TO 'jeffrey'@'localhost'; 

komandası işləyəcək və həqiqətən də yeni user yaradılacaq.

select user from mysql.user;

NO_ENGINE_SUBSTITUTION— deyək ki biz elə bir table yaratmaq istəyirik ki, hər hansı dəstəklənməyən storage engine-dan istifadə etsin (məsələn NDB). Təbii ki , əgər bu mode aktiv olmasa default engine istifadə olunacaq amma məncə ERROR çıxsa daha gözəl olar 🙂

create table t3 (id int) engine=NDB;

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION— bu 3 mode default olaraq MySQL installation-da yer alır.
Lakin bəzən bu default hallar bizə bəs etmir. Məsələn default olaraq “division by zero” əməliyyatı zamanı NULL insert və ya update olunur(mövzunun əvvəlində qeyd olunduğu kimi).

CREATE TABLE t1 (
id INT not null
);
insert into t1 values(1/0);

Adətən isə bizə bu sərf etmir. Yəni programlaşdırmadan belə görmüşük ki bu zaman ERROR çıxmalıdır özü də dəhşət məşhur bir ERROR 🙂
Və MySQL-in dəstəklədiyi ən ciddi mode “TRADİTİONAL”-dır ki, bu indiki halda bizə lazım olandır.

mysql> set @@session.sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode\G
*************************** 1. row ***************************
@@session.sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

İndi isə yenidən insert verək:

insert into t1 values(1/0);

Və sevimli ERROR-umuz bu zaman gəlir 🙂
TRADİTİONAL mode bizə Bir çox əlavə restriction-lar verir. Daha ətraflı documentation-dan oxumaq olar.

MySQL Server SQL Modes

Kateqoriyalar: MySQL Etiketlər: , ,