Archive

Posts Tagged ‘auto_increment’

Exploring Sequence in Oracle and MySQL

Bu yazıda hər 2 RDBMS-də Sequence (ardıcıllıq) anlayışlarına baxacıq.
İstifadə olunan versiyalar Oracle 11g R2, MySQL 5.6.x.
İlk öncə biraz teorik məlumatlar.
Sequence avtomatik olaraq bizim yerimizə, DB tərəfindən yaradılan unique rəqəmlər ardıcıllığıdır.
Çox zaman Primary Key (id) column-lar üçün istifadə olunan bu rəqəmlər, bizə təkrarsız ardıcıllıq verir ki, PK anlayışı da elə bundan ibarətdir.
Dolayısı ilə çox faydalıdır və demək olar ki əhəmiyyətli bütün cədvəllərdə istifadə olunur.
Ilk öncə Oracle-dan başlayacıq. Nümunə cədvəlimiz:

-- Oracle

create table t1(
id numeric,
firstname varchar2(25),
lastname varchar2(25),
primary key(id)
);

Gördüyümüz kimi, id bizdə PK-dır və biz istəyirik ki, hər dəfə ardıcıl ədədlər insert olunsun.
Sequence yaradaq:

create sequence id_seq;
>>>
sequence ID_SEQ created.

Yuxarıdakı create-imiz faktiki olaraq default olaraq aşağıdakı kimi icra olundu:

CREATE SEQUENCE  
"HR"."ID_SEQ"  
MINVALUE 1
MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 
START WITH 1 
CACHE 20 
NOORDER  
NOCYCLE ;

Bunu biraz izah edək. Default sequence creation:
* “HR”.”ID_SEQ” = burdan təbii ki, aydın olur ki, bizim sequence owner-i HR-dır.
* MINVALUE 1 = yəni bizim sequence-in minimum qiyməti 1 ola bilər.
* MAXVALUE 9999999999999999999999999999 = maximum qiymət
* INCREMENT BY 1 = yəni 1-1 artır.
* START WITH 1 = artırmağa 1-dən başla
* CACHE 20 = hər növbəti 20 ardıcıllığı cache-də saxla
* NOORDER = only relevant to clustered database
* NOCYCLE = dövr etmə. MINVALUE və yaxud MAXVALUE-ya çatdıqda error verəcək.

Və çox zaman elə bizə lazım olan göstəricilərlə sequence yaradıldı. Təkcə performance üçün CACHE size-ı artıra bilərsiniz.

* QEYD N1:
Sequence heç bir cədvələ təhkim olunmur, rollback və yaxud commit-ə tabe deyildir, hər hansı transaction-dan azaddır müstəqil olaraq artır.
*

Yuxarıdakı qeyddən belə nəticə çıxır ki, əgər 1 cədvəl üçün 10 ardıcıl rəqəm generate olunubsa 2ci bir cədvəl bundan sonra sequence-dən istifadə etsə 11-dən başlamalı olacaq.
Praktiki istifadəsinə baxaq. Sequence-dən ardıcıl rəqəmi əldə etmək məqsədilə nextval-dan istifadə etmək lazımdır:

insert into t1(id,firstname,lastname) values(id_seq.nextval,'shahriyar','rzayev');

Məlumata baxırıq:

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	 1 shahriyar		     rzayev

Eyni datanı 5 dəfə daha insert edək.

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	 1 shahriyar		     rzayev
	 2 shahriyar		     rzayev
	 3 shahriyar		     rzayev
	 4 shahriyar		     rzayev
	 5 shahriyar		     rzayev
	 6 shahriyar		     rzayev

6 rows selected.

Dahiyanə işləyir 😉

Sequence creation üzərində işləyərək artımın hansı ardıcıllıqla nədən başlayacağını və.s təyin edə bilərik.

Məsələn aşağıdakı sequence 10-dan 5-5 arta-arta davam edir.

-- 1
create sequence id_seq2 start with 10 increment by 5;

-- 2
truncate table t1;

-- 3
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
-- 4

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	10 shahriyar		     rzayev
	15 shahriyar		     rzayev
	20 shahriyar		     rzayev

* QEYD N2:
Sequence sıfırlamaq mümkün deyildir, bunun yeganə üsulu sequenci droplamaq və yenidən yaratmaqdır.
*

Burdan belə nəticə çıxır ki, hər hansı cədvəldə delete-dən yaranmış gap-ləri reorder etməklə doldurmaq mümkün deyil.
Yəni, 1, 2, 3, 4, 5, 20, 50 — gəlirsə deməli elə də gedəcək.

İndi isə MySQL-də sequence anlayışına baxaq..
AUTO_İNCREMENT = SEQUENCE.
MySQL-də fərqlər:
* AUTO_İNCREMENT hər cədvələ təhkim olunur.
* PK olmayan column-da create oluna bilmir.
* insert etdikdə auto_increment column-u qeyd etməyə ehtiyac yoxdur.
* NULL insert etdikdə increment olunaraq yeni value insert olunur.
* Ardıcıllığı reorder etmək olur.
və.s

Digər fərqlərə də baxacıq.
Nümunə cədvəlimiz:

-- MySQL

create table t1(
id int not null auto_increment,
firstname varchar(25),
lastname varchar(25),
primary key(id)
);

Gördüyümüz kimi, PK olan İD column-a cədvəli yaradarkən qeyd olunur.
İnsertlər edək:

insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  3 | shahriyar | rzayev   |
|  4 | shahriyar | rzayev   |
|  5 | shahriyar | rzayev   |
+----+-----------+----------+
5 rows in set (0.00 sec)

İnsert zamanı İD column-u qeyd etmədik.
İndi isə İD 3 və 4 olan sətrləri silək:

-- 1

delete from t1 where id=3 or id=4;

-- 2

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  5 | shahriyar | rzayev   |
+----+-----------+----------+
3 rows in set (0.00 sec)

1, 2, 5 = biz istəyirik ki, 1, 2, 3 kimi sıralansın.

-- 1
ALTER TABLE t1 DROP `id`;

-- 2
ALTER TABLE t1 AUTO_INCREMENT = 1;

-- 3
ALTER TABLE t1 ADD `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  3 | shahriyar | rzayev   |

Çox gözəl məqsədə çatdıq.
Oracle-da göstərdiyimiz kimi, MySQL-də də sequence-in hansı ədəddən başlıyacağını və nə qədər artacağını göstərə bilərik. Burda maraqlı məqam ondan ibarətdir ki, Global və Session olaraq verə bilərik. Global olaraq verildikdə RDBMS-də bütün cədvəllərdə olan auto_increment-lərə şamil olunacaq. Session based isə təbii ki, yalnız aktiv sessiya üçün geçərlidir.

-- 1 

mysql> set @@session.auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)

-- 2

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

-- 3
drop table t1;

-- 4

create table t1(
id int not null auto_increment,
firstname varchar(25),
lastname varchar(25),
primary key(id)
);

-- 5

mysql> insert into t1(firstname,lastname) values('shahriyar','rzayev'),('shahriyar','rzayev'),('shahriyar','rzayev'),('shahriyar','rzayev');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 6

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  5 | shahriyar | rzayev   |
| 15 | shahriyar | rzayev   |
| 25 | shahriyar | rzayev   |
| 35 | shahriyar | rzayev   |
+----+-----------+----------+
4 rows in set (0.00 sec)

Beləliklə auto_increment_incrementauto_increment_offset system dəyişənlərinin köməyi ilə biz 5-dən başlayaraq 10-10 artım əldə etdik.

Düşünürəm ki, ümumi mövzumuzu yekunlaşdırdıq.

Təşəkkürlər 😉

AUTO_INCREMENT

AUTO_INCREMENT-dən mənə elə gəlir ki, hamı istifadə edir çox məhşurdur. Artıq əl də öyrəşib id int AUTO_INCREMENT yazmağa. Bir çox maraqlı xüsusiyyətləri var ki, onları yazmağa çalışıram.
Bəli Adı üstündə auto increment yəni avtomatik artırma. Bu attribute bizləri, əziyyətli manual increment-dən azad edir. Həqiqətən də çox işimizə yarayır. Adi misallarla izah etməyə çalışaq. Table yaradaq:

create table auto_increment_test (
    id int auto_increment,
    name varchar(10)
);

🙂 ERROR-da da deyildiyi kimi, AUTO_INCREMENT olan column mütləq index-lənməlidir(ordakı key bu mənadadır). Bu index də adətən\həmişə PRİMARY KEY olur.

mysql> create table auto_increment_test (
    ->     id int auto_increment,
    ->     name varchar(10),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.15 sec)

Table yaratdıq indi də onun üzərində biraz işləyək.
İnsert edək:

insert into auto_increment_test(name) values('Shahriyar');

insert into auto_increment_test(name) values('Orxan');

insert into auto_increment_test(name) values('Elvin');

Və select edək:

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  3 | Elvin     |
+----+-----------+
3 rows in set (0.00 sec)

Həqiqətən də görürük ki, AUTO_INCREMENT İD-ni avtomatik artırır…Çox əlverişlidir…Manual olaraq:

insert into auto_increment_test(id,name) values(1,'Shahriyar');

insert into auto_increment_test(id,name) values(2,'Orxan');

insert into auto_increment_test(id,name) values(3,'Elvin');

yazmağa ehtiyac yoxdu…
İnsert-lərimizə dava edək:


insert into auto_increment_test(id,name) values(NULL,'Vusal');

insert into auto_increment_test(id,name) values(0,'Natiq');

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  3 | Elvin     |
|  4 | Vusal     |
|  5 | Natiq     |
+----+-----------+
5 rows in set (0.00 sec)

Gördüyümüz kimi AUTO_INCREMENT-li column-a 0 və NULL insert etmək istədikdə, 0 və NULL sadəcə iqnor edilir və adi qaydada avtomatik artırma baş verir.
*****************************************
İndiki halda bizim table-da 5 id var, yəni bunlar 1,2,3,4,5-dir…Indi isə İD-si 5 olan yəni sonuncı record-u silək:

delete from auto_increment_test where id=5;

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  3 | Elvin     |
|  4 | Vusal     |
+----+-----------+
4 rows in set (0.00 sec)

Və yeni bir insert edək:

insert into auto_increment_test(name) values('Mahmud');

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  3 | Elvin     |
|  4 | Vusal     |
|  6 | Mahmud    |
+----+-----------+
5 rows in set (0.00 sec)

Bəli yeni İD=6 oldu…Əslində fikirləşmək olardı ki, 4-dən sonra 5 gəlir və yeni İD də 5 olacaq amma baxmayaraq ki biz İD=5-i sildik MySQL internal olaraq increment-i yadda saxlayır…Yəni table-dan silinsə belə bir sonrakı increment məhz o silinən yerdən başlayır.

Mövzunu davam etdirək…Update edərək İD-3 olan recordun İD-sini=15 edək:

update auto_increment_test set id=15 where name='Elvin';

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  4 | Vusal     |
|  6 | Mahmud    |
| 15 | Elvin     |
+----+-----------+
5 rows in set (0.00 sec)

daha sonra insert edək:


insert into auto_increment_test(name) values('Necef');

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  4 | Vusal     |
|  6 | Mahmud    |
|  7 | Necef     |
| 15 | Elvin     |
+----+-----------+
6 rows in set (0.00 sec)

Qəribədir 🙂 Əslində mən də gözləyirdim ki, yeni İD=16 olacaq çünki sonuncu İD=15 idi amma yox elə olmadı..Məncə Update zamanı biz İD-ni birbaşa qeyd etdiyimiz üçün bu zaman MySQL bunu increment kimi qəbul etmir və öz implicit increment-inə qaldığı yerdən davam edir.
İndi isə İD-ni biz explicit olaraq insert zamanı verək:

insert into auto_increment_test(id,name) values(20,'Behmen');

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  4 | Vusal     |
|  6 | Mahmud    |
|  7 | Necef     |
| 15 | Elvin     |
| 20 | Behmen    |
+----+-----------+
7 rows in set (0.00 sec)

Və daha sonra da adi qaydada insert verək:

insert into auto_increment_test(name) values('Resad');

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Shahriyar |
|  2 | Orxan     |
|  4 | Vusal     |
|  6 | Mahmud    |
|  7 | Necef     |
| 15 | Elvin     |
| 20 | Behmen    |
| 21 | Resad     |
+----+-----------+
8 rows in set (0.00 sec)

İndi isə bayaqkının tam əksi olaraq təzə İD=21-dir 🙂 Explicit insert zamanı yəqin ki, MySQL-in implicit increment saygaci həmin yeni İD-yə keçir və məhz bu səbəbdəndir ki,yeni insert-lər zamanı increment yeni İD-dən başlayır.
****************************************
Daha bir maraqlı hadisə var. Bəs deyək ki, update vasitəsilə hər hansı bir İD-yə NULL qiymətini vermək istəyirik onda nə baş verəcək:

update auto_increment_test set id=null where name='Vusal';

mysql> select * from auto_increment_test;
+----+-----------+
| id | name      |
+----+-----------+
|  0 | Vusal     |
|  1 | Shahriyar |
|  2 | Orxan     |
|  6 | Mahmud    |
|  7 | Necef     |
| 15 | Elvin     |
| 20 | Behmen    |
| 21 | Resad     |
+----+-----------+
8 rows in set (0.00 sec)

Görürük ki, bu zaman yuxarıda bir yerlərdə qeyd etdiyimiz kimi NULL iqnor edilib, əvəzinə adi qaydada auto increment olunmur…İndiki halda NULL iqnor edilir və İD-nin qiyməti =0 olur. Və həmçinin Warning də qeydə alınır.

show warnings;

Warning', '1048', 'Column \'id\' cannot be null'

Çünki auto_increment column NOT NULL-dur. Yəni NULL qiymətlər qəbul edə bilməz…
Mövzuda Əsas yerlərə toxunduq 1 xırda mövzu qalıb ki onu da documentation-dan copy eliyib atıram bura :

When you reach the upper limit of an AUTO_INCREMENT column, an attempt to generate the next sequence value results in a duplicate-key error. This is a manifestation of MySQL’s general out-of-range value clipping behavior. For example, assume that you have a TINYINT UNSIGNED column as an AUTO_INCREMENT column and that it currently contains 254 as the maximum sequence value. The upper limit for this data type is 255, so the next insert generates a sequence value of 255 and successfully stores it in the new record. However, the insert after that fails because MySQL generates the next sequence value, which is 256. Because 256 is higher than the column’s upper limit of 255, MySQL clips 256 down to 255 and attempts to insert that value. But because 255 is already present in the table, a duplicate-key error occurs.”

Təşəkkürlər 🙂

Kateqoriyalar: MySQL Etiketlər: , ,