Archive

Archive for İ

Point-in-time recovery with MySQL row based binary log

İyun 28, 2013 2 şərh

/* Replication setup edənlər mütləq şəkildə binlog_format=row qeyd etməyi unutmasınlar */

Binary log-un nə olduğunu və hansı zamanlarda istifadə olunduğuna qısa olaraq toxunsaq əsasları:
1. Point-in-time recovery
2. Replication

Biz indi point-in-time recovery-yə baxacıq. Statement binary log formatında(default format)
hər şey çox sadədir yəni cədvələ edilən dəyişikliklər elə məhz statement şəklində bin log-a qeyd olunur. Statement bin log formatı üçün artıq bir yazım var : Statemen Binary log format

İndi isə row based binary log-la işləyək biraz.
Mövcud binary log-larımız tapaq:

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000031 |       120 |
+------------------+-----------+
1 row in set (0.13 sec)

Hansı hal hazırda istifadədir ona baxaq:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql-bin.000031 hal hazırda istifadədir. Position-u 120.

Cədvəl yaradaq və məlumat daxil edək:

mysql> create schema backup_test character set=utf8;
Query OK, 1 row affected (0.26 sec)

mysql> use backup_test;
Database changed

mysql> create table test_table(
    -> id int not null auto_increment,
    -> name varchar(15),
    -> surname varchar(20),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.74 sec)

mysql> insert into test_table(name,surname) values('Shahriyar','Rzayev'),('Khatai','Rzayev'),('Elvin','Binyatov');
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

Və hər hansı dəyişikliklərin qeyd olunduğuna baxaq:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 725
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Position 120-dən 725-ə dəyişdiyi üçün deyə bilərik ki binary log-a qeyd olunub. Binary log-a nəzər yetirək. Binary log-larla işləmək üçün mysqlbinlog utility default olaraq installation zamanı gəlir. Məndə installation path fərqli olduğu üçün məcburi full path verəcəm:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog /var/lib/mysql/data/mysql-bin.000031
.
.
/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F

BINLOG '
yivNURMBAAAAQgAAAFYCAAAAAEYAAAAAAAEAC2JhY2t1cF90ZXN0AAp0ZXN0X3RhYmxlAAMDDw8E
LQA8AAYxIFQC
yivNUR4BAAAAYAAAALYCAAAAAEYAAAAAAAEAAgAD//gBAAAACVNoYWhyaXlhcgZSemF5ZXb4AgAA
AAZLaGF0YWkGUnpheWV2+AMAAAAFRWx2aW4IQmlueWF0b3Z04Ry3
'/*!*/;
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of logfile

Burda diqqət yetirək:

BINLOG '
yivNURMBAAAAQgAAAFYCAAAAAEYAAAAAAAEAC2JhY2t1cF90ZXN0AAp0ZXN0X3RhYmxlAAMDDw8E
LQA8AAYxIFQC
yivNUR4BAAAAYAAAALYCAAAAAEYAAAAAAAEAAgAD//gBAAAACVNoYWhyaXlhcgZSemF5ZXb4AgAA
AAZLaGF0YWkGUnpheWV2+AMAAAAFRWx2aW4IQmlueWF0b3Z04Ry3

Bunun izahı:
By default, mysqlbinlog displays row events encoded as base-64 strings using BINLOG statements

Dolayısı ilə bas64 encoded string olduğu üçün biz görə bilmirik ki, konkret olaraq nə baş verib və nə düşüb bin log-a(bir daha qeyd edirəm ki bu row based binary log format-a aiddir).
Bizim anlaya biləcəyimiz bir dildə göstərilməsi üçün aşağıdakı qaydada decode edirik:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog -vv --base64-output=DECODE-ROWS /var/lib/mysql/data/mysql-bin.000031
.
.
/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Shahriyar' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Khatai' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Elvin' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Binyatov' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file

İndi isə fərqə diqqət edirik:

### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Shahriyar' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Khatai' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Elvin' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Binyatov' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

İnsert bizə aydın şəkildə göstərildi. İndi isə yuxarıdakıların nə anlama gəldiyini izah edək:
@1, @2, @3 column sırasıni göstərir. @N olaraq dava edir, harda ki N sütün sayıdır.

/* INT meta=0 nullable=0 is_null=0 */ İNT data tipini göstərir.
/* VARSTRING(45) meta=45 nullable=1 is_null=0 */ varstring=varchar.

İndi isə binary log-un nə dərəcədə əhəmiyyətli olduğunu nümayiş etdirək. Deyək ki, cədvəlimizi səhvən drop etmişik:

mysql> drop table test_table;
Query OK, 0 rows affected (0.24 sec)

mysql> select * from test_table;
ERROR 1146 (42S02): Table 'backup_test.test_table' doesn't exist

Və əlimizdə backup yoxdur. Lakin vaxtında binary log-umuzu aktiv etdiyimiz üçün bəxtimiz gətirib. Biz drop-a qədər olan bütün dəyişiklikləri replay edə bilərik. Bunun üçün binlog position-ları öyrənməliyik. Bir daha binary log-a nəzər yetirək:

/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1
###   @2='Shahriyar'
###   @3='Rzayev'
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2
###   @2='Khatai'
###   @3='Rzayev'
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3
###   @2='Elvin'
###   @3='Binyatov'
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
# at 725
#130628 11:48:11 server id 1  end_log_pos 862 CRC32 0xb911519a 	Query	thread_id=2	exec_time=1	error_code=0
SET TIMESTAMP=1372402091/*!*/;
DROP TABLE `test_table` /* generated by server */
/*!*/;

# at 252 -də başlayacıq # at 725-ə qədər replay edəcik. Yəni drop-a qədər:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog --start-position=252 --stop-position=725 /var/lib/mysql/data/mysql-bin.000031 | mysql -u root -p
Enter password:

Möcüzəli şəkildə cədvəlimiz və içindəki məlumatlar geri qayıtdı:

mysql> select * from test_table;
+----+-----------+----------+
| id | name      | surname  |
+----+-----------+----------+
|  1 | Shahriyar | Rzayev   |
|  2 | Khatai    | Rzayev   |
|  3 | Elvin     | Binyatov |
+----+-----------+----------+
3 rows in set (0.00 sec)

Bizim işlərimizi asanlaşdırmaq üçün və öz məlumat təhlükəsizliyinizi təmin etmək üçün mütləq binary log-u aktiv edin!
Təşəkkürlər 😉

Fibonacci ardıcıllığı

İyun 21, 2013 2 şərh

Biraz da maraq qataq işimizə ))
Bu procedure input olaraq verilən rəqəmin sayı qədər fibonacci sequence ardıcıllığı generate edir.

delimiter $$

create procedure fibonacci(n int)
begin
		declare n1 int default not null;
		declare n2 int default not null;
		declare n3 int default 0;
		declare seq text;
		declare count int default 0;

		set count=0;
		set n1=1;
		set n2=0;
		set n3=null;
		set seq='';

		while count<n do
				set count=count+1;
				set n3 = n1 + n2; 
				set n1=n2;
				set n2 = n3;
				set seq = (select CONCAT(seq,n3,', ') from dual);
		end while;
	
		SELECT seq AS fibonacci_numbers;
		
end $$

Və məsələn ilk 5 Fibonacci rəqəmləri:

mysql> call fibonacci(5);
+-------------------+
| fibonacci_numbers |
+-------------------+
| 1, 1, 2, 3, 5,    |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

İlk 10:

mysql> call fibonacci(10);
+------------------------------------+
| fibonacci_numbers                  |
+------------------------------------+
| 1, 1, 2, 3, 5, 8, 13, 21, 34, 55,  |
+------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Təşəkkürlər 😉

Emulating Oracle’s analytic function OVER() in MySQL

İyun 20, 2013 2 şərh

Oracle-da olub MySQL-də olmayan daha bir gözəl funksionallıq analytic funksiyalardır və onlardan çox xoşuma gələn:
Function(arg1,…, argn) OVER ( [PARTITION BY ] [ORDER BY ] [] )

Bu funksiya nə edir? Deyək ki, aşağıdakı cədvəlimiz var:

create table company (
    id tinyint not null auto_increment,
    com_group varchar(25) not null,
    com_name varchar(30) not null,
    emp_count int not null,
    primary key (id)
);

Və cədvəldə aşağıdakı məlumatlar var:

insert into company(com_group,com_name,emp_count) values('Tikinti','Yol-tikinti',20),('Tikinti','Bina-tikinti',30),('Tikinti','Korpu-tikinti',80);
insert into company(com_group,com_name,emp_count) values('Senaye','kimya-senaye-asc',40),('Senaye','metallurgiya-asc',500);
insert into company(com_group,com_name,emp_count) values('Xidmet','sosial-mudafie-asc',100),('Xidmet','ictimai-yardimc-asc',200);
insert into company(com_group,com_name,emp_count) values('Neft','azeriqaz',2000),('Neft','derin-ozuller',300),('Neft','gunesli',130);

Tapşırıq belədir ki, ~Hər müəssisənin işçi sayının ümümu müəssisə qrupunda olan işçi sayına nisbətini tapın~. Yəni, bizdə müəssisə qrupu Tikinti-dir və bu tikinti qrupunda toplam 3 müəssisə var. Hər müəssisədə 20,30 və 80 işçi çalışır. Toplam olaraq 130. bizə lazımdır ki, hər müəssisədə olan işçi sayını toplam işçi sayına bölərək nisbəti tapaq. 20/130, 30/130, 80/130 və.s bu şəkildə davam etsin.
Oracle-da bunu çox rahatlıqla yerinə yetirmək olur:

select id,com_group,com_name,emp_count,emp_count/sum(emp_count)
over (partition by com_group) from company;

Nəticədə isə aşağıdakı məlumatlar çıxmalıdır(Oracle installation-um mövcud olmadığı üçün test etməmişəm)

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.00 sec)

Çox gözəl Oracle bacarır ))) MySQL-də eyni nəticəni biraz daha əziyyətli üsulla almaq olur.
İlk öncə ağıla gələn ilk üsul:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  group by com_group)) as nisbet
from company as com_1;

ERROR 1242 (21000): Subquery returns more than 1 row

Təbriklər alınmadı )) Lakin biraz dəyişiklik etdikdə istədiyimiz nəticəni qismən əldə etmiş oluruq:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Tikinti' group by com_group)) as nisbet
from company as com_1 where com_group='Tikinti';
+----+-----------+---------------+-----------+--------+
| id | com_group | com_name      | emp_count | nisbet |
+----+-----------+---------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti   |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti  |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti |        80 | 0.6154 |
+----+-----------+---------------+-----------+--------+
3 rows in set (0.00 sec)

Və hər bir com_group üçün ayrılıqda bu nəticəni tapıb union all etsək yuxarıdakı nəticəni tamamilə almış olacıq:

(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Tikinti' group by com_group)) as nisbet
from company as com_1 where com_group='Tikinti')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Senaye' group by com_group)) as nisbet
from company as com_1 where com_group='Senaye')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Xidmet' group by com_group)) as nisbet
from company as com_1 where com_group='Xidmet')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Neft' group by com_group)) as nisbet
from company as com_1 where com_group='Neft');

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.02 sec)

Çox uzun və dəhşətli üsul olmağına baxmyaraq işləyir. Daha 2 üsul isə tamamilə bizim işimizə yarayacaq:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count / com_2.sum) as nisbet
from
    company as com_1
        inner join
    (select com_group, sum(emp_count) as sum from company group by com_group) as com_2
 ON com_1.com_group = com_2.com_group;

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.00 sec)

və biraz daha kompakt üsul:

select x.com_group, x.com_name, x.emp_count / sum(y.emp_count)
from 
(select * from company) as x 
inner join
(select  * from company) as y
on x.com_group = y.com_group
group by x.com_group,x.emp_count
order by x.id;

+-----------+---------------------+--------------------------------+
| com_group | com_name            | x.emp_count / sum(y.emp_count) |
+-----------+---------------------+--------------------------------+
| Tikinti   | Yol-tikinti         |                         0.1538 |
| Tikinti   | Bina-tikinti        |                         0.2308 |
| Tikinti   | Korpu-tikinti       |                         0.6154 |
| Senaye    | kimya-senaye-asc    |                         0.0741 |
| Senaye    | metallurgiya-asc    |                         0.9259 |
| Xidmet    | sosial-mudafie-asc  |                         0.3333 |
| Xidmet    | ictimai-yardimc-asc |                         0.6667 |
| Neft      | azeriqaz            |                         0.8230 |
| Neft      | derin-ozuller       |                         0.1235 |
| Neft      | gunesli             |                         0.0535 |
+-----------+---------------------+--------------------------------+
10 rows in set (0.03 sec)

Təşəkkürlər 😉

Union optimization (perfomance test)

*MySQL version: 5.6.12
Linux version: Ubuntu 12.04
Engine: InnoDB*

MySQL Performance test seriyalı yazılarımızdan növbətisi UNİON və UNİON ALL-dur.
İlk öncə biraz union-nun özündən bəhs edək.
sadə olaraq Union birləşdirmə aparır və result set olaraq 2 və yaxud daha artıq sorğunun birləşmiş halını qaytarır. Deyək ki, bizim cədvəl1-də aşağıdakı məlumatlar var:

blue
green
gray
black

və cədvəl2-də isə:

red
green
yellow
blue

UNION etsək MySQL internal olaraq bir temporary cədvələ hər 2 cədvəldən gələn result-ları sort edib yığacaq. Yəni bizim temprorary cədvəldə aşağıdakı məlumatlar toplanacaq:

black
blue
blue
gray
green
green
red
yellow

Daha sonra da məhz bu temporary cədvəldə olan təkrar məlumatlar silinəcək və son nəticədə user-ə aşağıdakı məlumatlar qaytarılacaq:

black
blue
gray
green
red
yellow

Dolayısı ilə yadda saxlamaq lazımdır ki, UNİON və UNİON ALL hər 2si temporary table-dan istifadə edir.

Test-lərimiz üçün biz sales adlı cədvəldən istifadə edəcik. 2.5 mln row-luq bu cədvəlin oxşarını yaradaq:

CREATE TABLE `sales` (
  `SALES_ID` int(8) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` decimal(8,0) NOT NULL,
  `PRODUCT_ID` decimal(8,0) NOT NULL,
  `SALE_DATE` datetime NOT NULL,
  `QUANTITY` decimal(8,0) NOT NULL,
  `SALE_VALUE` decimal(8,0) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) DEFAULT '0',
  `SALES_REP_ID` decimal(8,0) DEFAULT '0',
  `GST_FLAG` decimal(8,0) DEFAULT NULL,
  `sale_status` char(1) DEFAULT NULL,
  `FREE_SHIPPING` char(1) DEFAULT '',
  `DISCOUNT` decimal(8,0) unsigned DEFAULT '0',
  PRIMARY KEY (`SALES_ID`),
  KEY `sales_cust_idx` (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500004 DEFAULT CHARSET=latin1


create table sales2 like sales;

Daha sonra da sales_id-lərin hər 2 cədvəldə eyni olmaması(fərqli yerlərdə təkrarlanması üçün) məqsədilə onları procedure-la update edirik:

delimiter $$
 
CREATE PROCEDURE `insert_table`()
begin
	-- column deyisenleri
    declare v_SALES_ID int(8);
	declare v_CUSTOMER_ID decimal(8,0);
	declare v_PRODUCT_ID decimal(8,0);
	declare v_SALE_DATE datetime default NOT NULL;
	declare v_QUANTITY decimal(8,0) default NOT NULL;
    declare v_SALE_VALUE decimal(8,0) default NOT NULL;
    declare v_DEPARTMENT_ID decimal(8,0) DEFAULT '0';
    declare v_SALES_REP_ID decimal(8,0) DEFAULT '0';
    declare v_GST_FLAG decimal(8,0) DEFAULT NULL;
    declare v_sale_status char(1) DEFAULT NULL;
    declare v_FREE_SHIPPING char(1) DEFAULT '';
    declare v_DISCOUNT decimal(8,0) unsigned DEFAULT '0';
    
	-- sonuncu fetch olunmus row-nu tapan deyisen
    declare v_last_row_fetched int default 0;
     
    declare cursor1 cursor for select * from sales;
        
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 
			into v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,
					v_SALE_DATE,v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
					v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
			set v_SALES_ID=v_SALES_ID+1;

            insert into sales2()
			values(v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,v_SALE_DATE,
						v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
						v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT);
            
            
            
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
     
end$$

Və dərhal ilk query-mizi işə salaq yoxlayaq:

mysql> (select sales_id from sales)
    -> union
    -> (select sales_id from sales2);
.
.
.

2500004 rows in set (14.44 sec)

Nəyə görə 2500004 rows?
Axı bizdə:

mysql> select count(sales_id) from sales;
+-----------------+
| count(sales_id) |
+-----------------+
|         2500003 |
+-----------------+
1 row in set (0.96 sec)

mysql> select count(sales_id) from sales2;
+-----------------+
| count(sales_id) |
+-----------------+
|         2500003 |
+-----------------+
1 row in set (0.84 sec)

Lakin diqqətlə baxdıqda:

mysql> select max(sales_id) from sales2;
+---------------+
| max(sales_id) |
+---------------+
|       2500004 |
+---------------+
1 row in set (0.02 sec)

mysql> select max(sales_id) from sales;
+---------------+
| max(sales_id) |
+---------------+
|       2500003 |
+---------------+
1 row in set (0.00 sec)

dolayısı ilə bizdə 2500003 təkrar olunur lakin sonuncu id 2500004(1) distinct olduğu üçün,
2500003+1 olaraq toplam 2500004 row qaytarır. Bu da ona sübütdur ki, UNİON duplicate-ləri silir və yalnız DİSTİNCT məlumatları qaytarır. Temporary cədvəl-in yaranıb yaranmadığına baxmaq üçen isə status dəyişənlərinə diqqət yetirmək lazımdır:

1. mysql> flush status;

2. mysql> (select sales_id from sales) union (select sales_id from sales2);

3. mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Gördüyümüz kimi, Created_tmp_tables=1 temporary cədvəlimiz yaradıldı. Lakin burda daha təhlükəli hadisənin şahidi oluruq Created_tmp_disk_tables=1. Bu o deməkdir ki, query-nin işlədilməsi zamanı RAM yetərsizliyindən temporary table in-memory şəklindən disk şəklinə convert olunmuşdur. Yəni artıq temp table-ımız disk table olmuşdur ki, bu həmçinin əlavə vaxt itkisi deməkdir. Müvəqqəti olaraq yaradılan bu disk table həmçinin query-nin bitimindən sonra drop olunur ki, bu da əlavə vaxt tələb edir. Daha da incələməyə çalışsaq və profile etsək yuxarıda dediyimizin doğru olduğunu sübut edərik.

mysql> show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000125 |
| checking permissions      | 0.000010 |
| checking permissions      | 0.000008 |
| Opening tables            | 0.000110 |
| System lock               | 0.058612 |
| optimizing                | 0.000037 |
| statistics                | 0.000027 |
| preparing                 | 0.000019 |
| optimizing                | 0.000005 |
| statistics                | 0.000006 |
| preparing                 | 0.000007 |
| executing                 | 0.000005 |
| Sending data              | 1.568980 |
| converting HEAP to MyISAM | 2.008669 |
| Sending data              | 3.861828 |
| executing                 | 0.000027 |
| Sending data              | 6.042862 |
| optimizing                | 0.000029 |
| statistics                | 0.000020 |
| preparing                 | 0.000016 |
| executing                 | 0.000005 |
| Sending data              | 1.377518 |
| removing tmp table        | 0.555878 |
| Sending data              | 0.000028 |
| query end                 | 0.000016 |
| closing tables            | 0.000031 |
| freeing items             | 0.054344 |
| logging slow query        | 0.146203 |
| cleaning up               | 0.000048 |
+---------------------------+----------+
29 rows in set, 1 warning (0.09 sec)

Diqqətlə baxdıqda:
converting HEAP to MyISAM | 2.008669 – – – Yəni in-memory temp cədvəlin disk MyiSAM cədvələ çevrilməsi.
removing tmp table | 0.555878 – – – tmp cədvəlin silinməsi.

Təqribən 2.5-3 saniyə burada vaxt gedib.

Testlərimizə davam edək. Yuxarıda qeyd etdiyimiz kimi UNİON sort edib tmp table-a yığır nəticəni. Məntiqlə düşündükdə yaxşı olmaz mi ki, özümüz query-də sort edək? Bir növü UNİON-a kömək edək:

(select sales_id from sales order by sales_id)
union
(select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (13.99 sec)

Başqa şəkildə yazıb test etdikdə:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;
.
.
2500004 rows in set (14.62 sec)

14.44, 13.99 və 14.62 sonuncu order by-lı query-nin ümumiyyətlə order by-sıza yaxın sürətdə işləməsi hətta bəzən geri qalması çox maraqlıdır. Yəqin ki bu ona görədir ki 1 dəfə əlavə olaraq order edir. Bunun sübutunu Explain plana baxdıqda görmüş oluruq:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary; Using filesort


(select sales_id from sales)
union
(select sales_id from sales2);

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

Extra hissəsində olan Using filesort görülmüş əlavə sort-dan(order) xəbər verir.

Davam edək. Deyək ki bizə yalnız ilk 100 məlumat lazımdır. Biz bunu müxtəlif cür yaza bilərik.

(select sales_id from sales)
union
(select sales_id from sales2)
limit 100;
.
.
100 rows in set (13.61 sec)


(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id limit 100;
.
.
100 rows in set (13.73 sec)

select * from(
(select sales_id from sales order by sales_id asc)
union
(select sales_id from sales2 order by sales_id asc)
) as s where sales_id between 1 and 100;
.
.
100 rows in set (14.29 sec)


select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id asc limit 100) as s2
limit 100;
.
.
100 rows in set (0.00 sec)


(select sales_id from sales order by sales_id limit 100)
union
(select sales_id from sales2 order by sales_id limit 100)
limit 100;
.
.
100 rows in set (0.00 sec)

13.61, 13.73 , 14.29 və 0.00,0.00 sec. Sözsüz ki, fərq göz önündədir. Son 2 sorğu demək olar ki eynidirlər.
Davam edək. Desc olaraq order etmək istədikdə:

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc);

1
2
3
.
.
2500003
2500004

2,500,004 rows found. Duration for 1 query: 13.272 sec

Gördüyümüz kimi bu işləmədi. Subquery-lərin özündə desc olmasına baxmayaraq UNİON yenə də həmişəki kimi bizə asc qaytarır. Lakin aşağıdakı kimi yazdıqda:

(select sales_id from sales)
union
(select sales_id from sales2) order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.892 sec

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc)
order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.471 sec

Düzgün nəticə qaytarır.
İndi isə istəyirik ki, ilk sales-dən ilk 100-ü, sales2-dən isə son 100-ü union edək.

select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id desc limit 100) as s2;

(select sales_id from sales order by sales_id asc limit 100)
union
(select sales_id from sales2 order by sales_id desc limit 100);

200 rows found. Duration for 1 query: 0.001 sec.

Mövzunu genişləndirə bilərik amma mən ən son UNİON-nun tmp table problemi üçün bəzi həll yollarını göstərmək istərdim. Xüsusən də converting HEAP to MyISAM-ın qarşısını almaq üçün görülə biləcək tədbirlərə baxacıq.
Tmp table size-ı MySQL-də 2 system dəyişəni ilə idarə edə bilərsiniz:
tmp_table_sizemax_heap_table_size

İndiki halda bizim sorğumuz üçün my.cnf-də
tmp_table_size=128M
max_heap_table_size=128M

qeyd edirik və MySQL-ə restart veririk.

Daha sonra da sorğumuzu bir daha çalışdırırırq:

mysql> (select sales_id from sales order by sales_id) union (select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (9.53 sec)

Və dərhal status counter-lərər baxırıq ki, bəli bizim istədiyimiz kimi disk table yaradılmadı:
Created_tmp_disk_tables=0

mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.32 sec)

Profiling-də baxdıqda da bunun şahidi oluruq:

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000105 |
| checking permissions | 0.000011 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000122 |
| System lock          | 0.000022 |
| optimizing           | 0.000011 |
| statistics           | 0.000037 |
| preparing            | 0.000020 |
| optimizing           | 0.000006 |
| statistics           | 0.000009 |
| preparing            | 0.000009 |
| executing            | 0.000007 |
| Sending data         | 4.705173 |
| executing            | 0.000030 |
| Sending data         | 3.784227 |
| optimizing           | 0.000036 |
| statistics           | 0.000020 |
| preparing            | 0.000017 |
| executing            | 0.000004 |
| Sending data         | 1.043749 |
| removing tmp table   | 0.003071 |
| Sending data         | 0.000051 |
| query end            | 0.000019 |
| closing tables       | 0.000035 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000043 |
+----------------------+----------+
26 rows in set, 1 warning (0.00 sec)

Gördüyümüz kimi əvvəlki kimi converting HEAP to MyISAM yoxdur.
Həmçinin query-nin çalışma zamanında da çox azalma baş verdi:
13.99 – 9.53 = 4.46 sec

Əgər sizin də UNİON-la başınız ağrayırsa yuxarıda göstərdiyim üsullardan birindən istifadə edə bilərsiniz. Lakin tmp_table_size və max_heap_table_size-a toxunmazdan əvvəl ciddi düşünməniz lazımdır. Dokumentasiyada da deyildiyi kimi:

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

Dolayısı ilə ~çox həmişə yaxşıdır~ anlayışını bu dəyişənlər üçün işlətmək düzgün deyildir. Kifayət qədər RAM varsa və həqiqətən də inanirsınızsa ki, bu dəyişənlərin qiymətlərini dəyişmək sizə fayda verəcək o zaman sınaya bilərsiniz.

Ümumiyyətlə isə beynəlxalq məsləhət bu yöndədir ki, UNİON və UNİON ALL-dan mümkün mərtəbə qaçılsın. Çünki nə qədər sürətli çalışır çalışsın temp table-sız çalışmır.

Təşəkkürlər 😉

MySQL Workbench 6.0 what’s new?

MySQL Workbench 6-nın beta halını aşağıdakı linkdən yükləyə bilərsiniz.
MySQL Workbench
Yazı məqsədi aşkar olunmuş çox faydalı yenilikləri qeyd etməkdir.
1. İLk öncə elə dəyişilmiş HOME screen insanı cəlb edir:

home_screen

2. Connect olduqdan sonra əvvəlki versiyaların əksi olaraq Administration\Management-lə Development hissələri ayrı şəkildə deyil işləri asanlaşdırmaq məqsədilə bir yerdədirlər:


3. Yeni əlavə edilmiş SCHEMA İNSPECTOR ilə schema çox geniş şəkildə expose oluna bilir və metadata-nı rahatlıqla oxuya bilirsiniz. Həmçinin OPTİMİZE TABLE və ANALYSE TABLE funksiyaları da əlavə edilmişdir:

4. Yeni data search funksiyası ilə çox rahatlıqla lazım olan məlumatı tapmaq imkanı. Həmçinin Search-də istifadə olunmuş query-nin copy edilmə funksiyası da var bu şəxsən çox xoşuma gəldi:

Maraq üçün Workbench-in axtarış zamanı istifadə etdiyi sorğuya baxdım. Maraqlı bir metoddur deyə bilərik:

SELECT 
    id,
    IF(password = 'şəhriyar',password,'') AS password,
    IF(name = 'şəhriyar', name, '') AS name,
    IF(surname = 'şəhriyar', surname, '') AS surname,
    IF(fullname = 'şəhriyar',fullname,'') AS fullname,
    IF(about = 'şəhriyar', about, '') AS about,
    IF(email = 'şəhriyar', email, '') AS email,
    IF(alternative_email = 'şəhriyar',alternative_email,'') AS alternative_email,
    IF(address = 'şəhriyar', address, '') AS address,
    IF(city = 'şəhriyar', city, '') AS city,
    IF(country = 'şəhriyar', country, '') AS country,
    IF(phone = 'şəhriyar', phone, '') AS phone,
    IF(referer_1 = 'şəhriyar',referer_1,'') AS referer_1,
    IF(referer_2 = 'şəhriyar',referer_2,'') AS referer_2,
    IF(referer_3 = 'şəhriyar',referer_3,'') AS referer_3,
    IF(ext = 'şəhriyar', ext, '') AS ext,
    IF(request_key = 'şəhriyar',request_key,'') AS request_key
FROM
    new_multibon.wd_users
WHERE
    password = 'şəhriyar'
        OR name = 'şəhriyar'
        OR surname = 'şəhriyar'
        OR fullname = 'şəhriyar'
        OR about = 'şəhriyar'
        OR email = 'şəhriyar'
        OR alternative_email = 'şəhriyar'
        OR address = 'şəhriyar'
        OR city = 'şəhriyar'
        OR country = 'şəhriyar'
        OR phone = 'şəhriyar'
        OR referer_1 = 'şəhriyar'
        OR referer_2 = 'şəhriyar'
        OR referer_3 = 'şəhriyar'
        OR ext = 'şəhriyar'
        OR request_key = 'şəhriyar'
LIMIT 100;

5. Çox zaman bizə eyni tip cədvəlləri yaratmaq lazım olur. Bunun üçün hazır cədvəl template-ləri create edib saxlayıb, daha sonra da onların üzərindən yeni əlavə edilmiş create table like ilə tez bir şəkildə yarada bilərik:

6. vertical output
sorğunu seçib ctrl+g etməklə siz standart output əvəzinə vertical və daha səliqəli output əldə etmiş olursunuz bu xüsusən status-lara baxanda çox faydalıdır.

7. Yeni inkişaf etdirilmiş Administration Panel:

8. vƏ PHP developer-lər üçün xüsusi bir maddə ayırmışam. Hər hansı sorğu üçün connection və php query-ni automatic olaraq yaradıb istifadə edə bilərsiniz:

Yaradılacaq kodlar aşağıdakı kimidir:

$host="127.0.0.1";
$port=3306;
$socket="";
$user="root";
$password="";
$dbname="testing";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();


$query = "select * from test_encoding";


if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($field1, $field2);
    while ($stmt->fetch()) {
        //printf("%s, %s\n", $field1, $field2);
    }
    $stmt->close();
}

Open Source və rəsmi məhsul olan MySQL Workbench mənə digər məhsullardan daha rahat və daha professional gəlir və gündəlik olaraq geniş community tərəfindən istifadə olunmaqdadır.

Təşəkkürlər 😉

Emulating Oracle’s PIVOT operator in MySQL

İyun 13, 2013 2 şərh

Bu yaxınlarda lazım olan bir halda MySQL-də tapa bilmədiyim, lakin Oracle-da mövcud olan PİVOT-dan danışacıq və bunun “workaround”-unu göstərəcik.
İlk öncə PİVOT nədir? Deyək ki aşağıdakı şəkildə cədvəlimiz var:

CREATE TABLE pivot_test (
  id            NUMBER,
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  quantity      NUMBER
);

Və Cədvəl daxilində aşağıdakı məlumatlar var:

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);


SELECT * FROM pivot_test;

        ID CUSTOMER_ID PRODU   QUANTITY
---------- ----------- ----- ----------
         1           1 A             10
         2           1 B             20
         3           1 C             30
         4           2 A             40
         5           2 C             50
         6           3 A             60
         7           3 B             70
         8           3 C             80
         9           3 D             90
        10           4 A            100

10 rows selected.

Tapşırıq belədir ki, A , B və C product-unun ayrıca toplam QUANTITY-sini tapmalıyıq. Yəni bu cədvəldə toplam olaraq A , B və C-nin QUANTITY-si ayrılıqda nə qədərdir?
Oracle-da bu çox asan hazır şəkildə tapılır:

SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
           210             90            160

1 row selected.

Gördüyünüz kimi cədvəldə A , B və C ayrıca hesablandı.
MySQL-də belə bir operator yoxdur lakin həll yolu var.

CREATE TABLE pivot_test (
  id            int,
  customer_id   int,
  product_code  VARCHAR(5),
  quantity      int
);

-- insertlər yuxarıdakı qaydada

mysql> select * from pivot_test;
+------+-------------+--------------+----------+
| id   | customer_id | product_code | quantity |
+------+-------------+--------------+----------+
|    1 |           1 | A            |       10 |
|    2 |           1 | B            |       20 |
|    3 |           1 | C            |       30 |
|    4 |           2 | A            |       40 |
|    5 |           2 | C            |       50 |
|    6 |           3 | A            |       60 |
|    7 |           3 | B            |       70 |
|    8 |           3 | C            |       80 |
|    9 |           3 | D            |       90 |
|   10 |           4 | A            |      100 |
+------+-------------+--------------+----------+
10 rows in set (0.00 sec)

Və eyni nəticəni biz aşağıdakı sorğu ilə ala bilirik:

select
sum(case when product_code='A' then quantity end) as a_sum_quantity,
sum(case when product_code='B' then quantity end) as b_sum_quantity,
sum(case when product_code='C' then quantity end) as c_sum_quantity
from pivot_test;

+----------------+----------------+----------------+
| a_sum_quantity | b_sum_quantity | c_sum_quantity |
+----------------+----------------+----------------+
|            210 |             90 |            160 |
+----------------+----------------+----------------+
1 row in set (0.09 sec)

Bu üsulu yadda saxladıqdan daha advance bir çalışmanı həll edə bilərik.
Tapşırıq bundan ibarətdir ki, yaş aralıqlarına uyğun olaraq qadın və kişilərin nə qədər hesablaşma(xərcləmə)
apardıqlarını tapmaq lazımdır. Yəni deyək ki, 16 yaşında 5 kişi və 3 qadın var. 16 yaşlı kişilərin toplam harcaması ayrı, 16 yaşlı qadınların toplam nə qədər harcama apardığını ayrıca tapmaq lazımdır. Aşağıdakı sorğu ilə bunu çox asand tapa bilirik:

select (year(now())-year(birthday)) as `age`,
round(sum(case when gender=1 then sum end),2) as kisi_sum,
round(sum(case when gender=0 then sum end),2) as qadin_sum
from user_sum_statistics
where wu_status!=2 and wt_status!=2
group by `age`;
.
.
+------+----------+-----------+
| age  | kisi_sum | qadin_sum |
+------+----------+-----------+
|   16 |     6.00 |      NULL |
|   17 |     NULL |      4.80 |
|   18 |    27.90 |      NULL |
|   19 |     NULL |    222.20 |
|   20 |   158.80 |     27.00 |
|   21 |    74.20 |     74.98 |
|   22 |    40.30 |    718.18 |
|   23 |   118.40 |     26.00 |
|   24 |   271.92 |   1984.65 |
|   25 |     NULL |   1245.39 
.
.

Gördüyünüz kimi hesablanır və doğru cavab çıxır. Lakin 16 yaşlı qadın, 17 yaşlı kişi və.s yaşlı harcama aparmayan insanlar olduğu üçün NULL-lar çıxır. Hesablama-da NULL-un olması xoşagələn olmadığı üçün çox gözəl olardı ki NULL-ların yerinə 0 hesablansın.
Buna da MySQL imkanları ilə nail olmaq olar:

select (year(now())-year(birthday)) as `age`,
if(sum(case when gender=1 then sum end) is null,0,round(sum(case when gender=1 then sum end),2)) as kisi_sum,
if(sum(case when gender=0 then sum end) is null,0,round(sum(case when gender=0 then sum end),2)) as qadin_sum
from user_sum_statistics
where wu_status!=2 and wt_status!=2
group by `age`;

+------+----------+-----------+
| age  | kisi_sum | qadin_sum |
+------+----------+-----------+
|   16 |     6.00 |      0.00 |
|   17 |     0.00 |      4.80 |
|   18 |    27.90 |      0.00 |
|   19 |     0.00 |    222.20 |
|   20 |   158.80 |     27.00 |
|   21 |    74.20 |     74.98 |
|   22 |    40.30 |    718.18 |
|   23 |   118.40 |     26.00 |
|   24 |   271.92 |   1984.65 |
|   25 |     0.00 |   1245.39 |

Daha başqa üsul və inkişafetdirmələri(improvements) olanlar mütləq yazsınlar.

Təşəkkürlər 😉

Installing MySQL 5.6.12 on Ubuntu 12.04

* Elə qəbul edirik ki, bizim install fresh install-dır yəni başqa versiya MySQL-imiz yoxdur *

5.6.x versiya mysql Ubuntu repo-da olmadığı üçün bunu manual olaraq install etməli oldum.
Və bu zülmü bir daha heç kim çəkməsin deyə dərhal bir yazı hazırladım.

* ilk öncə bunu qeyd etmək lazımdır ki, .deb-lə yazılan MySQL 5.6.12 default installation path-i öyrəşdiyimizin əksinə olaraq /usr/mysql/bin deyil, /opt/mysql/server-5.6-dir.

İlk öncə MySQL 5.6.12-ni yükləyək: MySQL 5.6.12

Daha sonra da ardıcıllıqla aşağıdakı komandaları keçirdək:
1.

 apt-get install libaio1

Download etdiyimiz direktoriyaya gedib:
2.

 dpkg -i  mysql-5.6.12-debian6.0-x86_64.deb

Daha sonra my.cnf faylı yaradırıq:
3.

cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server
cp /opt/mysql/server-5.6/support-files/my-default.cnf /etc/my.cnf

Bu komandalardan birincisi bizə imkan verəcək ki MySQL-i service mysql.server start\stop\restart-la idarə edək. Həm də linux restart-dan sonra auto olaraq MySQL start olacaq.
İkinci komanda isə default my.cnf faylının yerini /etc/my.cnf-ə dəyişir (kopyalayır).

4.
/etc/my.cnf faylının içinə aşağıdakıları qeyd edib save edirik:

basedir = /opt/mysql/server-5.6
datadir = /var/lib/mysql

5.

echo 'export PATH=$PATH:/opt/mysql/server-5.6/bin' | sudo tee /etc/profile.d/mysql.server.sh

6.

groupadd mysql
useradd mysql -g mysql

(Əgər əvvəlcədən mysql install etmisinizsə onda buna ehtiyac yoxdur.)

7.

chown -R mysql /opt/mysql/server-5.6/
chgrp -R mysql /opt/mysql/server-5.6/

8. install edirik. log file-larının yaradılması və.s

/opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql

9. MySQL-i start edirik

service mysql.server start

10. root user-in parolunu dəyişirik

 /opt/mysql/server-5.6/bin/mysqladmin -u root password '12345'

11. connect oluruq:

root@sh-ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.12-log MySQL Community Server (GPL)

12.

service mysql.server stop
rm /opt/mysql/server-5.6/support-files/my-default.cnf
service mysql.server start

Təbriklər!!! 😉 Ubuntu-nun şəxsən bu halı heç xoşuma gəlmədi Centos-da yeni versiya MySQL install edib test etmək qat-qat daha rahatdır.