نکاتی در زمینه استفاده راحت تر از پایگاه داده MySQL

مارس

سلام $\':)\'$

در طول چند سال کار با پایگاه داده MySQL تعدادی Tips (نکته یا راهنما) یاد گرفتم که کار با آن را برای من ساده تر می کردند. مطلب امروز تعدادی از این نکات را معرفی می کند.

نمایش عمودی نتایج
برای مشاهده عمومی نتایج یک query به جای ; از \\G در انتهای آن استفاده کنید. مانند:

mysql> select * from PARTITIONS limit 2 \\G
*************************** 1. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: information_schema
                   TABLE_NAME: CHARACTER_SETS
               PARTITION_NAME: NULL
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: NULL
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: NULL
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: NULL
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 384
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 16604160
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: NULL
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP:
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: information_schema
                   TABLE_NAME: COLLATIONS
               PARTITION_NAME: NULL
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: NULL
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: NULL
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: NULL
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 231
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 16704765
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: NULL
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP:
              TABLESPACE_NAME: NULL

استفاده از --safe-updates
با استفاده از پارامتر --safe-updates (:معادل --i-am-a-dummy یا -U) می توانید یک حصار امنیتی برای query های مربوط به update و delete فعال کنید. بدین صورت که اگر query حذف یا ویرایش شما where نداشته باشد، اجرا نمی شود. مانند:

mysql> delete from meta;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

یا

mysql> update meta set meta_key = \'my_key\';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

همچنین شما می توانید به جای استفاده از پارامتر --safe-updates از دستور SET در MySQL استفاده نمایید. مانند:

mysql> SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

دستور بالا علاوه بر فعال کردن safe updates ، حداکثرهایی نیز برای محدودیت select ساده و انتخاب چندین جدولی اعمال می کند.

فعال سازی auto complete و بروزرسانی cache آن
بعد از نصب MySQL در بسیاری از موارد قابلیت auto complete یا همان «کامل کردن خودکار» فعال است. پارامتر مربوط به فعال سازی آن --auto-rehash می باشد. همچنین شما می توانید در خط فرمان MySQL با تایپ \\# این قابلیت را فعال کنید. کاربرد دیگر \\# بروزرسانی کش auto completion بعد از انجام تغییرات بر روی پایگاه داده می باشد.
*برای کامل کردن یک خط کافیست دکمه Tab را فشار دهید. در صورتی که نتایج برای کامل کردن یک عبارت بیش از یک مورد باشد، با زدن دو tab پشت سر هم، فهرست این نتایج را مشاهده می فرمایید.

مشاهده فهرست کامل فرایندهای در حال اجرا
برای مشاهده فرایندهای در حال اجرا کافیست دستور show processlist را در خط فرمان وارد کنید. همچنین برای نمایش کامل query در حال اجرا می توانید پارامتر full را به این دستور اضافه کنید. مانند:

mysql> show full processlist;
+------+------+-----------+---------+---------+------+-------+-----------------------+
| Id   | User | Host      | db      | Command | Time | State | Info                  |
+------+------+-----------+---------+---------+------+-------+-----------------------+
| 3101 | root | localhost | efs_tmp | Query   |    0 | NULL  | show full processlist |
+------+------+-----------+---------+---------+------+-------+-----------------------+

کشتن (kill) یک فرایند
در ستون اول خروجی دستور show processlist آی دی مربوط به فرآیند در حال اجرا را مشاهده کردید. برای متوقف ساختن این فرایند کافیست دستور kill را به صورت زیر اجرا کنیم:

mysql> kill 3101;

مشاهده وضعیت MySQL و برخی اطلاعات بیشتر
برای مشاهده وضعیت کلی MySQL شامل تنظیمات و پارامتر های فعال شده از دستور show status استفاده می کنیم. مانند:

mysql> show status;
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 3         |
| Aborted_connects                  | 0         |
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                  | 0         |
| Bytes_received                    | 1346      |
..........
........
......
...
.

همچنین برای مشاهده وضعیت INNODB از دستور SHOW ENGINE INNODB STATUS استفاده نمایید.
دستور show table status نیز برای مشاهده وضعیت جداول استفاده می شود. مانند:

mysql> show table status like \'%user%\' \\G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 14
 Avg_row_length: 80
    Data_length: 1128
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-11-21 18:07:11
    Update_time: 2011-02-27 14:57:28
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

* از like برای محدود ساختن نتایج استفاده کرده ام.

دسترسی سریع به وضعیت پایگاه داده
برای دسترسی سریع به پایگاه داده کافیست دستور \\s را در خط فرمان MySQL وارد نمایید:

mysql> \\s
--------------
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686) using readline 6.1

Connection id:		3103
Current database:	mydb_tmp
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		\'\'
Using delimiter:	;
Server version:		5.1.49-1ubuntu8.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			12 hours 39 min 28 sec

Threads: 1  Questions: 27846  Slow queries: 2  Opens: 7711  Flush tables: 1  Open tables: 64  Queries per second avg: 0.611
--------------

دستور show create
به طور کلی دستور show create برای مشاهده نحوه ساخت یک جدول، پایگاه داده، تابع و… استفاده می شود. مانند:

mysql> show create database mysql \\G
*************************** 1. row ***************************
       Database: mysql
Create Database: CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */

یا

mysql> show create table time_zone \\G
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum(\'Y\',\'N\') NOT NULL DEFAULT \'N\',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=\'Time zones\'

ساخت یک جدول از ترکیب چند جدول
یکی از انواع engine های موجود در MySQL انجین MERGE یا MRG_MyISAM می باشد. این engine برای ترکیب چندین جدول در یک جدول مورد استفاده قرار می گیرد.
در زیر ما دو جدول با انجین MyISAM و یک جدول total با انجین MERGE از ترکیب دو جدول اول ساخته ایم:

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

در صورت وارد کردن اطلاعات به t1 و t ویا بروزرسانی اطلاعات آن ها، امکان دستیابی به اطلاعات از طریق جدول total وجود دارد. مانند:

mysql> INSERT INTO t1 (message) VALUES (\'Testing\'),(\'table\'),(\'t1\');
mysql> INSERT INTO t2 (message) VALUES (\'Testing\'),(\'table\'),(\'t2\');

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

دستور mysqlshow برای دسترسی سریع به فهرست اطلاعات
شما می توانید برای دسترسی سریع به فهرست پایگاه های داده، جداول و ستون های آنها از دستور mysqlshow استفاده نمایید. مانند:

$ mysqlshow -uroot
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mydb_tmp            |
| mydb               |
| mysql              |
| radius             |
| security           |
+--------------------+

یا

$ mysqlshow -uroot mysql
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

یا

$ mysqlshow -uroot mysql servers
Database: mysql  Table: servers
+-------------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Server_name | char(64) | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| Host        | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Db          | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Username    | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Password    | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Port        | int(4)   |                 | NO   |     | 0       |       | select,insert,update,references |         |
| Socket      | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Wrapper     | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
| Owner       | char(64) | utf8_general_ci | NO   |     |         |       | select,insert,update,references |         |
+-------------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

مرتب سازی همراه با ارجحیت
در صورتی که قصد دارید علاوه بر مرتب سازی یک query انتخاب، ارجحیتی نیز به بعضی از ریف های آن بدهید، کافیست از ترکیب IN() و order by استفاده کنید. در مثال زیر علاوه بر مرتب سازی بر اساس ستون iso_code به ردیف های IR و US ارجحیت و وزن بالاتری داده ایم:

mysql>SELECT * FROM countries ORDER by iso_code IN (\'IR\', \'US\') desc;
+----------+----------------------------------------+
| iso_code | name                                   |
+----------+----------------------------------------+
| IR       | IRAN                         |
| US       | United States                          |
| AF       | Afghanistan                            |
| AL       | Albania                                |
| DZ       | Algeria                                |
| AS       | American Samoa                         |
+----------+----------------------------------------+

امیدوارم که این مطلب راهنمای خوبی برای استفاده راحت تر و مفید تر از پایگاه داده MySQL باشد.

موفق باشید $\'8)\'$

$\'\'$

درباره سعید زبردست

من برنامه نویس Java و PHP هستم. در کنار برنامه نویسی اطلاعات خوبی در زمینه پایگاه ‌های داده بخصوص MySQL دارم. برای اطلاع بیشتر به صفحه درباره سعید زبردست مراجعه کنید.

$\"Google+\"/$ $\"Facebook\"/$ $\"Twitter\"/$ $\"Identi.ca\"/$ اشتراک‌گذاری