بایگانی دسته: پایگاه داده MySQL

پایگاه داده MySQL

نحوه بازیابی رمز عبور MySQL

مقدمه

به دلایل زیادی ممکن است که شما نتوانید با کاربر root وارد محیط پایگاه داده MySQL شوید. مواردی از قبیل فراموشی کلمه عبور، تغییر کلمه عبور توسط شخص دیگر (همکار یا نفوذگر)، نفوذ به پایگاه داده بدون داشتن رمز عبور و غیره. راهنمای زیر در نسخه 5.5 پایگاه داده MySQL بر روی سیستم عامل اوبونتو 12.04 آزموده شده است. همچنین این راهنما بر روی سایر سیستم‌عامل‌ها با کمی تغییر محیطی قابل اجرا می‌باشد.

تغییر رمز MySQL بدون داشتن رمز فعلی

مراحل زیر را به ترتیب برای تغییر رمز MySQL انجام دهید.

۱- متوقف کردن سرویس MySQL

دستور زیر را برای متوقف کردن سرویس MySQL در خط فرمان گنو/لینوکس اجرا نمایید:

sudo /etc/init.d/mysql stop

۲- راه‌اندازی سرویس MySQL در حالت غیر فعال کردن رمز عبور

دستور زیر را برای راه‌اندازی سرویس MySQL در حالت غیر فعال کردن رمز عبور در خط فرمان گنو/لینوکس اجرا نمایید:

sudo mysqld_safe --skip-grant-tables &

۳- ورود به محیط MySQL

دستور زیر را برای ورود به محیط MySQL بدون نیاز به رمز عبور در خط فرمان گنو/لینوکس اجرا نمایید:

mysql -u root

۴- تغییر رمز کاربر root

دستورات زیر را در خط فرمان MySQL برای تغییر رمز عبور کاربر root وارد نمایید. به جای عبارت NEW-ROOT-PASSWORD رمز مورد نظر خود را جایگزین نمایید. همچنین شما میتوانید رمز سایر کاربران را نیز با دستورات مشابه تغییر دهید.

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

۵- متوقف کردن سرویس جاری MySQL

دستور زیر را برای متوقف کردن سرویس جاری MySQL در خط فرمان گنو/لینوکس اجرا نمایید:

sudo /etc/init.d/mysql stop

۶- راه‌اندازی سرویس MySQL و ورود با رمز جدید

دستور زیر را برای راه‌اندازی سرویس MySQL در خط فرمان گنو/لینوکس اجرا نمایید:

sudo /etc/init.d/mysql start

در نهایت با استفاده از رمز عبور جدید و دستور زیر وارد محیط MySQL شوید:

mysql -u root -p

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

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

پارتیشن بندی اطلاعات در پایگاه داده

سلام،

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

پارتیشن بندی پایگاه داده چیست؟

منظور از پارتیشن‌بندی، تقسیم داده‌ها به قسمت‌های مستقل‌تر است. به عنوان مثال اگر در جدولی فهرست اشخاص را نگه می دارید، می توانید آن‌ها را با معیارهایی همچون جنسیت، سال تولد و… در همان جدول دسته‌بندی نمایید. این کار سبب افزایش کارایی و در نتیجه دسترس پذیری بیشتر داده‌ها می شود. در ادامه این مطلب، توضیحات بیشتری در رابطه با دلیل افزایش کارایی ارائه خواهد شد.

معیارهای دسته‌بندی اطلاعات برای پارتیشن‌بندی

در نوع پایگاه‌های داده رابطه‌ای معیارها و ضوابط گوناگونی جهت تقسیم پایگاه داده وجود دارد. برای این منظور، ابتدا یک کلید پارتیشن‌بندی (Partitioning Key) را تعریف کرده و سپس بر اساس آن و معیارهای مشخص داده‌ها را پارتیشن‌های مختلف تقسیم می‌کنند. عمومی‌ترین معیارها عبارتند از:

پارتیشن‌بندی محدوده‌ای (Range partitioning)

انتخاب پارتیشن براساس محدوده آن و کلید داده شده صورت می‌پذیرد. فرض کنید که اطلاعات کدپستی ده رقمی در پایگاه داده در ۹ دسته ۱۰۰۰۰۰۰۰۰۰ تقسیم شده اند. در صورتی که کلید داده شده در یکی از این ۹ محدوده باشد، فقط اطلاعات موجود در آن محدوده مورد بررسی قرار خواهند گرفت. به عنوان مثال کلید (کدپستی) ۶۸۹۵۶۳۲۶۵۳ مشخص می کند که فقط محدوده ۶۰۰۰۰۰۰۰۰۰ تا ۶۹۹۹۹۹۹۹۹۹ بررسی شود. یعنی تقریبا ۱/۹ کل اطلاعات جدول مورد نظر.

پارتیشن‌بندی فهرست‌وار (List partitioning)

پارتیشن‌ها بر اساس یک فهرست مشخص می‌شوند. به عنوان مثال فهرست استان‌های غربی، شرقی، مرکزی و… . مثلا استان‌های آذربایجان غربی، آذربایجان شرقی، اردبیل و زنجان می‌توانند پارتیشنی با عنوان استان‌های شمال‌غریی تشکیل دهند. در صورتیکه استان یک آدرس اردبیل باشد، فقط داده‌های استان‌های شمال‌غربی بررسی می‌شوند.

پارتیشن‌بندی درهم (Hash partitioning)

در این نوع پارتیشن‌بندی، داده‌ها بر اساس یک کلید و تابع درهم‌سازی (hash function) به پارتیشن‌های مختلف تقسیم می‌شوند.

پارتیشن‌بندی کلیدی (Key partitioning)

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

پارتیشن‌بندی ترکیبی (Composite partitioning)

همانگونه که از نام وی مشخص است، پارتیشن‌بندی ترکیبی، مجموعه‌ای از انواع پارتیشن‌بندی‌ها است.

روش‌های ذخیره اطلاعات پارتیشن‌بندی شده

پارتیشن‌بندی، اطلاعاتی مانند کل پایگاه داده و یا جداول، ردیف‌ها و… را تقسیم کرده و در قطعات کوچک‌تر ذخیره می‌کند. برای تقسیم کردن ردیف‌های موجود در یک جدول می‌توان به صورت افقی (Horizontal) و عمودی (Vertical) عمل کرد.
در حالت افقی (Horizontal) ردیف‌های مختلف در جدول‌های جداگانه ذخیره می‌شوند. به عنوان مثال در جدول مشتریان، افرادی که کدپستی آنان کمتر از 50000 باشد در جدول مشتریان شرقی (CustomerEast) و باقی افرادی که دارای کدپستی بالای 50000 می‌باشند در جدول مشتریان غربی (CustomerWest) ذخیره می‌شوند. همچنین برای دستیابی به تمامی مشتریان می توان با استفاده از UNION یک VIEW ایجاد کرد که شامل تمامی ردیف‌ها باشد.
در حالت عمودی (Vertical) ستون‌های کمتری در جدول اصلی قرار می‌گیرند و بقیه ستون‌ها به صورت متناظر در جدول‌های دیگر قرار می‌گیرند. عمومی‌ترین کاربرد این روش جداکردن ستون‌هایی با اطلاعات نسبتا ثابت و ستون‌هایی با اطلاعات متغیر است. نسخه فعلی MySQL 5.6 از حالت عمودی پشتیبانی نمی‌شود.

چه زمانی و چرا به پارتیشن‌بندی پایگاه داده نیاز داریم؟

در موارد زیر استفاده از پارتیشن‌بندی به افزایش محسوس بازدهی می‌انجامد:

  • داده‌های خیلی زیاد
  • کمبود حافظه RAM
  • داده‌هایی با دسته‌بندی‌های مشخص

هنگام اجرای کوئری (Query) در صورتیکه جدول مورد نظر پارتیشن‌بندی نشده باشد، دستور بر روی کل جدول اجرا می‌شود. مانند:

حال اگر جدول مورد نظر را پارتیشن‌بندی نمایید، هنگام اجرای Query، محدوده‌ی بررسی فقط پارتیشن(های) مرتبط را شامل می‌شود. دستورات قبلی با فرض پارتیشن‌بندی جدول، محدوده‌های مشخص شده زیر را بررسی می‌نماید:

در ادامه این مطلب به پیاده‌سازی پارتیشن‌بندی در پایگاه داده مای‌اس‌کیوال براساس نسخه 5.6 به طور خلاصه می‌پردازم.

پیاده‌سازی پارتیشن‌بندی در MySQL


بهترین راه برای فراگیری هر مهارتی، مطالعه راهنمای خود منبع است. مبحث پارتیش‌بندی اطلاعات در پایگاه داده بسیار گسترده بوده و دارای نکات ریز و درشت زیادی می‌باشد که پرداختن به همه‌ی آن‌ها از حوصله این مقاله خارج است. با این حال به صورت خلاصه نحوه ایجاد پارتیشن‌ها در زیر ارائه شده‌اند.

نحوه ساخت پارتیشن محدوده‌ای (Range partitioning)

CREATE TABLE t1 (
  id int 
) ENGINE=InnoDB
  # or MyISAM, ARCHIVE
PARTITION BY RANGE (id)
(
  PARTITION P1 VALUES LESS THAN (10),
  PARTITION P2 VALUES LESS THAN (20)
)

نحوه ساخت پارتیشن فهرست‌وار (List partitioning)

CREATE TABLE t1 (
  id int 
) ENGINE=InnoDB
PARTITION BY LIST (id)
(
  PARTITION P1 VALUES IN (1,2,4),
  PARTITION P2 VALUES IN (3,5,9)
)

نحوه ساخت پارتیشن درهم (Hash partitioning)

CREATE TABLE t1 (
  id int not null primary key
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 10;

نحوه ساخت پارتیشن کلیدی (Key partitioning)

CREATE TABLE t1 (
  id int not null primary key
) ENGINE=InnoDB
PARTITION BY KEY ()
PARTITIONS 10;

در تمامی موارد بالا پارتیشن توسط ستونی از نوع عدد (INTEGER) مشخص شده است. با این حال شما می‌توانید به جای این ستون با رعایت موارد زیر پارتیشن‌بندی را بسط دهید:

  • اگر ستونی از به صورت Unique Key یا Primary Key تعریف شده باشد، پارتیشن‌بندی باید این ستون در بر گیرد.
  • در ساخت پارتیشن می توانید به جای یک ستون، از عبارت (شامل توابع MySQL) استفاده نمایید. با این شرط که عبارت مورد نظر مقدار عددی را بازگرداند.
  • در صورت ندانستن محدود عددی می توانید از عبارت MAXVALUE برای بازه بالا استفاده نمایید.
  • حداکثر تعداد پارتیشن‌ها 210=1024 می باشد.

برای درک بیشتر موارد بالا با نحوه ساخت پارتیشن در جدول های t1 و t2 در زیر توجه فرمایید:

CREATE TABLE t1 (
  d date
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(d))
(
  PARTITION P1 VALUES 
    LESS THAN (1999),
  PARTITION P2 VALUES 
    LESS THAN (2005),
  PARTITION P3 VALUES 
    LESS THAN (2010),
  PARTITION P4 VALUES 
    LESS THAN (MAXVALUE)
)
CREATE TABLE t2 (
  d date
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(d))
(
  PARTITION P1 VALUES 
    LESS THAN (TO_DAYS('1999-01-01')),
  PARTITION P2 VALUES 
    LESS THAN (TO_DAYS('2005-01-01')),
  PARTITION P3 VALUES 
    LESS THAN (TO_DAYS('2010-01-01')),
  PARTITION P4 VALUES 
    LESS THAN (MAXVALUE)
)

پارتیشن‌بندی چقدر در سرعت اجرای دستورات تاثیر دارد؟

برای بررسی تاثیر پارتیشن‌بندی نیاز به یک پایگاه داده با مقدار قابل توجهی اطلاعات داریم. برای این منظور از پروژه MySQL Employees Test Database که شامل حدودا ۴ میلیون رکورد در ۶ جدول است استفاده کرده‌ایم. در زیر ساختار این پایگاه داده را مشاهده می‌کنید:

به جستار (Query) زیر و زمان اجرای آن توجه فرمایید:

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
+----------+
| COUNT(*) |
+----------+
|   260957 |
+----------+
1 row in set (1.07 sec)

و همان دستور بعد از پارتیشن‌بندی:

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
+----------+
| COUNT(*) |
+----------+
|   260957 |
+----------+
1 row in set (0.32 sec)

برای مشاهده عملکرد پارتیشن‌بندی برای افزایش سرعت حذف اطلاعات به جستار (Query) زیر و زمان اجرای آن توجه فرمایید:

DELETE FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';
Query OK, 247489 rows affected (10.57 sec)

و همان دستور حذف بعد از پارتیشن‌بندی:

ALTER TABLE salaries DROP PARTITION p15;
Query OK, 0 rows affected (1.35 sec)

نحوه پارتیشن‌بندی جدول salaries در پایگاه داده employees

در زیر دستور پارتیشن‌بندی جدول حقوق‌ها (salaries) به صورت محدوده‌ای (RANGE) بر اساس تاریخ سال from_date مشاهده می‌فرمایید:

ALTER TABLE salaries 
PARTITION BY RANGE (YEAR(from_date))
(
    PARTITION p1 VALUES LESS THAN (1985),
    PARTITION p2 VALUES LESS THAN (1986),
    PARTITION p3 VALUES LESS THAN (1987),
    PARTITION p4 VALUES LESS THAN (1988),
    PARTITION p5 VALUES LESS THAN (1989),
    PARTITION p6 VALUES LESS THAN (1990),
    PARTITION p7 VALUES LESS THAN (1991),
    PARTITION p8 VALUES LESS THAN (1992),
    PARTITION p9 VALUES LESS THAN (1993),
    PARTITION p10 VALUES LESS THAN (1994),
    PARTITION p11 VALUES LESS THAN (1995),
    PARTITION p12 VALUES LESS THAN (1996),
    PARTITION p13 VALUES LESS THAN (1997),
    PARTITION p14 VALUES LESS THAN (1998),
    PARTITION p15 VALUES LESS THAN (1999),
    PARTITION p16 VALUES LESS THAN (2000),
    PARTITION p17 VALUES LESS THAN (2001),
    PARTITION p18 VALUES LESS THAN (2002),
    PARTITION p19 VALUES LESS THAN (2003),
    PARTITION p20 VALUES LESS THAN (MAXVALUE)
)

اگر با خطای Cannot delete or update a parent row: a foreign key constraint fails مواجه شدید، با دستور زیر کلید خارجی salaries_ibfk_1 را حذف نمایید:

ALTER TABLE salaries DROP FOREIGN KEY salaries_ibfk_1;

منابع و اطلاعات بیشتر

شاد و موفق باشید :)

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

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

انواع داده در MySQL


سلام،
هدف این مقاله بررسی انواع داده ها در پایگاه داده MySQL (مای‌اس‌کیوال) می باشد. MySQL انواع مختلف داده را در دسته بندی های زیر پشتیبانی می کند:

  • اعداد (Numeric)
  • زمان (Date and Time)
  • رشته ها (String)
  • داده های فضایی (Spatial)

در این مقاله داده های عددی، زمان و رشته ها را بررسی می کنیم. با توجه به تخصصی بودن داده های فضایی، این نوع داده ها را در مقاله ای جداگانه در آینده بررسی خواهیم کردم.

اعداد (Numeric)

MySQL تمامی انواع داده های عددی استاندارد SQL شامل اعداد صحیح و اعداد تقریبی را پشتبانی می کند. هر یک از نوع داده های عددی حافظه مشخصی برای ذخیره سازی نیاز دارند. همچنین در نظر داشته باشید که هر کدام از آنها محدوده مشخصی را پشتیبانی می کنند. هر نوع از این داده های عددی را می توان به صورت SIGNED یا UNSIGNED تعریف کرد. در حالت SIGNED این ستون ها از مقادیر منفی پشتبیبانی می کنند ولی در حالت UNSIGNED اعداد ذخیره شده باید بزرگتر ویا مساوی صفر باشند. در نظر داشته باشید که نوع داده BOOLEAN برای ذخیره مقادیر TRUE و FALSE همان TINYINT(1) می باشد.
جدول زیر اطلاعات کلی نوع داده های عددی را در حالت SIGNED و UNSIGNED نشان می دهد:

اطلاعات کلی نوع داده های عددی Integer
نوع حافظه (بایت) حداقل مقدار حداکثر مقدار توضیحات
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INTEGER 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
اطلاعات کلی نوع داده های عددی شناور
نوع حافظه (بایت) مقدار نحوه تعریف توضیحات
FLOAT 4 از ۰ تا ۲۳ رقم FLOAT(M,D) مقدار تقریبی (Approximate Value)
DOUBLE 8 از ۲۴ تا ۵۳ رقم DOUBLE(M,D) مقدار تقریبی (Approximate Value)
DECIMAL هر ۹ رقم ۴ بایت به صورت باینری تا ۶۵ رقم DECIMAL(M,D) مقدار دقیق (Exact Value)

در جدول بالا حرف M نشان‌دهنده تعداد کل رقم‌های داده و حرف D نشان‌دهنده تعداد رقم‌های بعد از ممیز (.) می باشد. به عنوان مثال در ستونی که به صورت FLOAT(7,4) تعریف شده است می توان حداکثر مقدار 999.9999 را ذخیره نمود. در صورتی که شما عدد 999.00009 را به این جدول وارد کنید، عدد واردشده به صورت 999.0001 ذخیره خواهد شد.

سوال: منظور از مقدار تقریبی و دقیق در جدول داده های عددی شناور چیست؟
پاسخ: گاهی اوقات در نوع داده‌های تقریبی اعداد گرد می شوند. در زیر با اجرا کردن چند دستور در خط فرمان MySQL پایگاه داده‌ای با دو ستون Decimal و Float تعریف کردیم. در آخرین دستور نتیجه گردشدن را در ستون Float مشاهده می فرمایید:

mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
@a := (a/3): 33.333333333
@b := (b/3): 33.3333333333333
     @a * 3: 99.999999999
     @b * 3: 100

برای اطلاعات بیشتر به صفحه Problems with Floating-Point Values در سایت MySQL مراجعه نمایید.
در نسخه های قبل از 5.0.3 نوع داده DECIMAL به صورت String ذخیره می شود و مقدار رقم‌هایی که پشتیبانی می کرد مانند نوع داده DOUBLE بود.

نوع داده BIT

این نوع داده برای ذخیره بیت‌های ۰ و ۱ استفاده می شود. برای تعریف ستونی از این نوع از عبارت BIT(M) استفاده می کنیم که در اینجا M فضایی به طول تقریبا (M+7)/8 بایت را برای نگهداری داده‌ها ایجاد می کند. M می تواند عددی از ۱ تا ۶۴ باشد. برای نشان‌دادن ۰ و ۱ به صورت بیت از حرف b استفاده می کنیم. به عنوان مثال b'111' و b'10000000' که به ترتیب برابر اعداد ۷ و ۱۲۸ می باشند. برای درک بیشتر این موضوع به دستورات و نتایج زیر در خط فرمان MySQL توجه فرمایید:

mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';
mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
|    5 | 101      | 5        | 5        |
+------+----------+----------+----------+
3 rows in set (0.03 sec)

آخرین Query در بالا توابع مختلف برای تبدیل این اعداد را نشان می دهد. عبارت +0 مقدار دودویی ذخیره شده را به عدد ده‌دهی متناظر تبدیل می کند.

زمان (Date and Time)

در کل ۵ نوع داده برای ذخیره زمان (ساعت و تاریخ) داریم. در جدول زیر اطلاعات این نوع داده‌ها را مشاهده می‌فرمایید:

اطلاعات کلی نوع داده های زمانی (Date and Time)
نوع حافظه (بایت) مقدار صفر (“Zero” Value)
DATE 3 '0000-00-00'
TIME 3 '00:00:00'
DATETIME 8 '0000-00-00 00:00:00'
TIMESTAMP 4 '0000-00-00 00:00:00'
YEAR 1 0000

در ادامه به بررسی نوع داده‌های ذکر شده در بالا خواهیم پرداخت.

نوع داده DATE

این نوع داده فقط برای ذخیره سازی تاریخ استفاده می شود. فرمت پیش فرض این نوع داده 'YYYY-MM-DD' است. بازه‌ای که توسط DATE پشتیبانی می شود از '1000-01-01' تا '9999-12-31' می باشد.

نوع داده TIME

این نوع داده برای ذخیره سازی زمان (ساعت، دقیقه، ثانیه) استفاده می شود. فرمت پیش‌فرض این نوع داده 'HH:MM:SS' و یا 'HHH:MM:SS' است. بازه ای که در نوع TIME پشتیبانی می شود از '-838:59:59' تا '838:59:59' می باشد. نوع داده TIME فقط برای نمایش زمان روز استفاده نمی شود. برای نمایش تفاوت زمانی دو بازه و یا زمان باقی‌مانده نیز می تواند از TIME استفاده نمود. به همین دلیل بازه پشتیبانی شده بیشتر از ۲۴ ساعت و یا کمتر از صفر (منفی) می باشد.
برای استفاده از عبارت های مخفف در نوع داده TIME باید دقت نمایید. به عنوان مثال MySQL عبارت '11:12' را به صورت '11:12:00' تفسیر می کند. در صورتی که داده بدون ':' (دو نقطه) بیان شود، MySQL آن را از سمت راست تفسیر می کند. به عنوان مثال عبارت '1112' و یا 1112 به معنی '00:11:12' است.
به صورت پیش فرض MySQL داده‌های خارج از محدوده TIME را در صورتی که با فرمت مناسب وارد شده باشند به نزدیک‌ترین بازه تبدیل می کند. به عنوان مثال عبارت‌های '-850:00:00' و '850:00:00' به '-838:59:59' و '838:59:59' تبدیل خواهند شد. داده‌هایی که فرمت مناسب نداشته و یا قابل تفسیر برای داده TIME نباشند به عبارت '00:00:00' تبدیل خواهند شد.

نوع داده DATETIME

این نوع داده برای ذخیره سازی زمان به صورت کامل شامل تاریخ و ساعت استفاده می شود. فرمت پیش فرض این نوع داده 'YYYY-MM-DD HH:MM:SS' می باشد. بازه‌ی پشتیبانی DATETIME از '1000-01-01 00:00:00' تا '9999-12-31 23:59:59' است.

نوع داده TIMESTAMP

این نوع داده همانند DATETIME می باشد. بازه‌ای که توسط TIMESTAMP پشتیبانی می‌شود از '1970-01-01 00:00:01' UTC تا '2038-01-19 03:14:07' UTC است.
داده‌های TIMESTAMP هنگام ذخیره شدن از زمان منطقه زمانی (Time Zone) کنونی سیستم به UTC تبدیل و ذخیره می شوند. متناظر با همین امر، هنگام نشان دادن داده‌های TIMESTAMP، ابتدا داده‌ها از UTC به منطقه زمانی کنونی سیستم تبدیل شده و بعد نمایش داده می شوند. به همین علت در صورتی که منطقی زمانی سیستم عوض شود، ممکن است داده‌ها به صورت غیرعادی و یا غلط نمایش داده شوند.
نوع داده TIMESTAMP این قابلیت را دارد که مقداردهی اولیه و یا مقداردهی خودکار داشته باشید. برای مقداردهی اولیه کافیست مقدار پیش فرض فیلد مورد نظر را CURRENT_TIMESTAMP قرار دهید. مانند:

mysql> CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

در حالت مقداردهی خودکار، مقدار ذخیره شده در فیلد TIMESTAMP هنگام بروزرسانی (UPDATE) یک ردیف به زمان بروزرسانی تغییر پیدا می کند. مانند:

mysql> CREATE TABLE t (name VARCHAR(100), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

mysql> INSERT INTO t(name) VALUES('Saeid');

mysql> SELECT * FROM t;
+-------+---------------------+
| name  | ts                  |
+-------+---------------------+
| Saeid | 2011-12-22 12:00:26 |
+-------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE t SET name = 'Alireza';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+---------+---------------------+
| name    | ts                  |
+---------+---------------------+
| Alireza | 2011-12-22 12:01:56 |
+---------+---------------------+
1 row in set (0.00 sec)

برای کسب اطلاعات بیشتر به صفحه TIMESTAMP Properties در سایت MySQL مراجعه نمایید.

نوع داده YEAR

این نوع داده برای ذخیره سازی سال با فرمت YYYY به کار می‌رود. شما می تواند سال مورد نظر خود را به صورت دو رقمی YEAR(2) و یا ۴ رقمی YEAR(4) تعریف کنید. نوع YEAR از سال 1901 تا 2155 و 0000 را پشتیبانی می کند. در صورتی که سال را به صورت ۲ رقمی تعریف کرده باشید، اعداد 00 تا 69 در قرن ۲۱ و اعداد 70 تا 99 در قرن ۲۰ در نظر گرفته می شوند. مثلا 45 به عنوان 2045 و 80 به صورت 1980 در نظر گرفته می شود. تمامی مقادیر غیر منطقی برای YEAR به صورت 0000 ذخیره می شوند.

رشته ها (String)

نوع داده String برای نگه داری رشته‌ها (متون) استفاده می شود. در زیر اطلاعات کلی داده هایی از نوع String را مشاهده می فرمایید. در جدول زیر M نشان‌دهنده طول تعریف شده برای داده و L نشان‌دهنده طول واقعی رشته داده شده می باشد.

اطلاعات کلی نوع داده رشته‌ها (String)
نوع مقدار حافظه (بایت)
CHAR(M) 0 <= M <= 255 M × w بایت. w تعداد بایت مورد نیاز برای حداکثر طول کاراکترهای تعریف شده در character set جدول می باشد.
BINARY(M) 0 <= M <= 255 M بایت
VARCHAR(M) 0 <= M <= 65532 L + 1 بایت اگر داده مورد نظر کمتر از ۲۵۵ بایت نیاز داشته باشد.
L + 2 بایت اگر داده مورد نظر بیشتر از ۲۵۵ بایت نیاز داشته باشد.
VARBINARY(M) 0 <= M <= 65532 M + 1 بایت اگر داده مورد نظر کمتر از ۲۵۵ بایت نیاز داشته باشد.
M + 2 بایت اگر داده مورد نظر بیشتر از ۲۵۵ بایت نیاز داشته باشد.
TINYBLOB L < 28 L + 1 بایت
TINYTEXT L < 28 L + 1 بایت
BLOB L < 216 L + 2 بایت
TEXT L < 216 L + 2 بایت
MEDIUMBLOB L < 224 L + 3 بایت
MEDIUMTEXT L < 224 L + 3 بایت
LONGBLOB L < 232 L + 4 بایت
LONGTEXT L < 232 L + 4 بایت
ENUM('v1','v2',...) حداکثر ۶۵,۵۳۵ عضو ۱ یا ۲ بایت.
وابسته به مقدار داده.
SET('v1','v2',...) حداکثر ۶۴ عضو ۱، ۲، ۳، ۴ یا ۸ بایت
وابسته به مجموعه داده شده.

نوع داده CHAR و VARCHAR

CHAR و VARCHAR بسیار شبیه به هم هستند. مهم‌ترین تفاوتی که بین CHAR و VARCHAR وجود دارد نحوه ذخیره سازی و بازیابی اطلاعات است. هنگام تعریف این نوع داده‌ها شما حداکثر تعداد کاراکتری را که می خواهید ذخیره شود را مشخص می نمایید. به عنوان مثال CHAR(30) برای ذخیره حداکثر ۳۰ کاراکتر تعریف می شود.
طول ستونی که از نوع CHAR باشد ثابت است. در صورتی که طول داده کمتر از طول تعریف شده باشد، فضای باقیمانده با فاصله (space) از راست پر می‌شود. هنگام بازیابی اطلاعات این فضای خالی قبل از نمایش حذف می‌شود مگر اینکه حالت PAD_CHAR_TO_FULL_LENGTH فعال شده باشد.
در نوع داده VARCHAR طول داده ذخیره شده به اندازه مقدار داده وارد شده می باشد. به عنوان مثال اگر ستونی به صورت VARCHAR(100) تعریف شده باشد ولی طول رشته داده شده 50 کاراکتر باشد، فقط به مقدار بایت زیر فضا لازم است.

(50 * فضای لازم برای ذخیره هر کاراکتر) + 2


در صورتی که در تعریف VARCHAR(M) مقدار M بیشتر از ۶۵,۵۳۵ باشد، MySQL به طور خودکار نوع ستون را از VARCHAR به نوع مناسبی مانند MEDIUMTEXT تغییر می دهد.

جدول زیر تفاوت‌های فضای مورد نیاز برای ذخیره اطلاعات را در CHAR و VARCHAR نمایش می دهد. دقت کنید که در جدول زیر Character Set داده مورد نظر تک بایتی مانند latin1 است.

بررسی تفاوت فضای مورد نیاز برای CHAR(4) و VARCHAR(4)
داده CHAR(4) فضای مورد نیاز VARCHAR(4) فضای مورد نیاز
'' '    ' ۴ بایت '' ۱ بایت
'ab' 'ab  ' ۴ بایت 'ab' ۳ بایت
'abcd' 'abcd' ۴ بایت 'abcd' ۵ بایت
'abcdefgh' 'abcd' ۴ بایت 'abcd' ۵ بایت

نوع داده BINARY و VARBINARY

این نوع داده ها مانند CHAR و VARCHAR می باشند فقط با این تفاوت که در این نوع داده‌ها رشته‌های باینری (صفر و یک) ذخیره می شود. در نتیجه Character set برای این نوع داده‌ها معنی ندارد. از طرفی مرتب‌سازی و مقایسه بر اساس مقدار عددی رشته باینری ذخیره شده می باشد.

نوع داده BLOB و TEXT

این نوع داده‌ها مانند BINARY و VARBINARY می باشند. هر کدام از این داده‌ها دارای ۴ نوع می‌باشند که تنها تفاوت آن‌ها در حداکثر طول داده قابل ذخیره شدن است.

نوع داده ENUM

این نوع داده فقط امکان ذخیره سازی رشته ای را می دهد که از مجموعه تعریف شده باشد. این مجموعه هنگام ساخت جدول تعریف می شود. مانند جدول زیر:

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

در ستون name در جدول بالا فقط می توانید یکی از مقادیر small، medium یا large را ذخیره نمایید. در صورتی که مقداری خارج از مجموعه وارد مجموعه شود، فضای خالی در name ذخیره می شود، نه مقدار وارد شده.

نوع داده SET

این نوع داده مانند ENUM می باشد، با این تفاوت که شما می توانید چند عضو از مجموعه تعریف شده را انتخاب نمایید. توجه نمایید که یا داده‌های تکراری قبل از ذخیره سازی حذف می شوند. به مثال زیر توجه فرمایید:

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO myset (col) VALUES  ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.00 sec)

از قراردادن فاصله قبل و یا بعد ',' جدا خوددداری نمایید.

چطور نوع داده مناسب را انتخاب نماییم؟

قبل از هر چیز بازه‌ی مورد نیاز برای داده خود را معین نمایید. سپس مختصرترین نوع داده را برای ستون خود انتخاب نمایید. به عنوان مثال برای بازه اعداد ۰ تا ۹۹۹۹۹ نوع MEDIUMINT UNSIGNED بهترین گزینه است.
مورد دیگر انتخاب نوع ستون مناسب با داده شما است. به عنوان مثال هرگز تاریخ شمسی را در پایگاه داده در ستونی از نوع رشته (مانند CHAR) ذخیره ننمایید. بهترین گزینه تبدیل تاریخ به تاریخ میلای و ذخیره آن در داده‌ای از نوع DATE است.
در هنگام تعریف ستونی برای داده‌های عددی در صورتی که فقط اعداد مثبت را لازم دارید، ستون خود را از نوع UNSIGNED انتخاب نموده و حداقل بازه را انتخاب نمایید. به عنوان مثال اگر ستون عددی شما کلید اصلی و افزایشی خودکار است (PRIMAY KEY and AUTO_INCREMENT) حتما نوع داده خود را UNSIGNED انتخاب نمایید تا حداکثر مقدار کلید اصلی دو برابر شود. نوع INTEGER SIGNED حداکثر عدد 2147483648 (حدود ۲ میلیارد) و INTEGER UNSIGNED حداکثر عدد 4294967295 (حدود ۴ میلیارد) را پشتیبانی می کند.

روش دیگر استفاده از PROCEDURE ANALYSE به منظور دریافت پیشنهاد خود پایگاه داده است. PROCEDURE ANALYSE با توجه به داده‌های فعلی، پیشنهاد خود را برای بهینه کردن نوع داده ارائه می دهد. در نظر داشته باشید که هر چه تعداد داده فعلی شما بیشتر باشد، PROCEDURE ANALYSE پیشنهاد بهتر و دقیق‌تری می دهد. نحوه استفاده از PROCEDURE ANALYSE بسیار ساده است. نحوه نگارش آن به صورت زیر است:

mysql> SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);

مانند:

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

یا

SELECT * FROM table1 PROCEDURE ANALYSE() \G

اسامی مشابه

در پایگاه داده MySQL برخی از انواع داده‌ها با نام‌های مشابه دیگری نیز قابل دسترسی می باشند. یکی از دلایل این کار پیاده سازی راحت‌تر SQL های پایگاه‌های داده‌ی دیگر است. در زیر این اسامی را مشاهده می نمایید.

اسامی مشابه برای نوع داده‌ها در MySQL
نوع داده اسم مشابه
INTEGER INT
DECIMAL DEC
DECIMAL FIXED
DECIMAL NUMERIC
DOUBLE REAL
TINYINT BOOLEAN
TINYINT BOOL
VARCHAR(M) CHARACTER VARYING(M)
FLOAT FLOAT4
DOUBLE FLOAT8
TINYINT INT1
SMALLINT INT2
MEDIUMINT INT3
INT INT4
BIGINT INT8
MEDIUMBLOB LONG VARBINARY
MEDIUMTEXT LONG VARCHAR
MEDIUMTEXT LONG
MEDIUMINT MIDDLEINT

منابع

شاد و موفق باشید :)

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

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

نکاتی در زمینه استفاده راحت تر از پایگاه داده 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 دارم. برای اطلاع بیشتر به صفحه درباره سعید زبردست مراجعه کنید.

بدست آوردن کشور یک آدرس IP به کمک MySQL

سلام :)

مطلب امروز روشی برای بدست آوردن کشور یک آدرس IP (بخوانید آی پی) با استفاده از پایگاه داده MySQL را آموزش می دهد. در این مطلب ابتدا یک جدول که حاوی نام کشور و محدوده IP های آن می باشد ایجاد می کنیم. سپس با تعریف یک تابع، امکان تشخیص سریع کشور مرتبط با هر آی پی را ارائه می دهیم.

IP چیست؟

به نقل از صفحه «نشانی پروتکل اینترنت» در ویکی پدیا، دانشنامه آزاد:

نشانی پروتکل اینترنت (Internet Protocol Address) یا به اختصار نشانی آی‌پی (IP Address) نشانی‌ عددی است که به هریک از دستگاه ها و رایانه‌های متصل به شبکه ی رایانه ای که بر مبنای نمایه TCP/IP (از جمله اینترنت) کار می‌کند، اختصاص داده می‌شوند. پیام‌هایی که دیگر رایانه‌ها برای این رایانه می‌فرستند با این نشانه ی عددی همراه است و راه یاب های شبکه آن را مانند «نشانی گیرنده» در نامه‌های پستی تعبیر می‌کنند، تا بالاخره پیام به رابط شبکه رایانه مورد نظر برسد.

جدول پایگاه داده

من اطلاعات مربوط به محدوده آدرس های آی پی و کشور مرتبط با آنها در جدولی با نام ip_location ذخیره کرده ام. برای ایجاد این جدول دستور زیر را در خط فرمان MySQL وارد نمایید:

CREATE TABLE `ip_location` (
  `from_ip` int(15) DEFAULT NULL,
  `to_ip` int(15) DEFAULT NULL,
  `country` varchar(32) DEFAULT NULL,
  KEY `from_ip` (`from_ip`,`country`),
  KEY `to_ip` (`to_ip`,`country`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

مرحله بعد وارد کردن فهرست کشورها و محدوده آی پی های آن هاست. برای سادگی کار من یک خروجی (Dump) از دستور ایجاد جدول ip_location و اطلاعات موجود در آن تهیه کرده ام.
دریافت ip_location.sql با حجم 165KB
در صورتی که آشنایی با وارد کردن (import) چنین فایل هایی ندارید، پیشنهاد می کنم مطلب «راهنمای وارد کردن(import) و صادر کردن(export) در MySQL» را مطالعه کنید.

تابع getIpCountry

مرحله بعد تعریف تابعی برای محاسبه آدرس ۳۲ بیتی هر آی پی و مقایسه آن با محدوده های موجود در جدول ip_location است. برای تعریف این تابع دستورات زیر را در خط فرمان MySQL وارد کنید:

DELIMITER $$
CREATE FUNCTION getIpCountry(ip varchar(15)) RETURNS varchar(64)
BEGIN
 declare a tinyint unsigned;
 declare b tinyint unsigned;
 declare c tinyint unsigned;
 declare d tinyint unsigned;
 declare total bigint;
 declare result varchar(64);
 select substring_index(ip, '.', 1 ) into a;
 select substring_index(substring_index(ip , '.', 2 ),'.',-1) into b;
 select substring_index(substring_index(ip , '.', -2 ),'.',1) into c;
 select substring_index(ip, '.', -1 ) into d;
 set total := (a*256*256*256) + (b*256*256) + (c*256) + d;
 select SQL_CACHE country into result from ip_location where total between from_ip and to_ip limit 1;
 if (result is null) or (result = '') then
 set result := 'unknown';
 end if; 
 return result;
 END$$
DELIMITER ;

بدست آوردن کشور

برای مشاهده کشور مرتبط با هر آی پی کافیست تا با استفاده از دستور SELECT تابع getIpCountry را فراخوانی نماییم. مانند:

mysql> SELECT getIpCountry('79.175.165.171');
+--------------------------------+
| getIpCountry('79.175.165.171') |
+--------------------------------+
| IRAN (ISLAMIC REPUBLIC OF)     |
+--------------------------------+
1 row in set (0.03 sec)

mysql> SELECT getIpCountry('4.2.2.4');
+-------------------------+
| getIpCountry('4.2.2.4') |
+-------------------------+
| UNITED STATES           |
+-------------------------+
1 row in set (0.00 sec)

ایده استفاده

فرض کنید که شما سایتی به دو زبان فارسی و انگلیسی دارید. با استفاده از این روش امکان تشخیص کشور بازدیدکننده برای شما بوجود می آید. در نتیجه می توانید کاربر بیننده را به صورت پیش فرض به سایت انگلیسی یا فارسی خود راهنمایی کنید. این روش قابل تعمیم به چندین زبان می باشد.

پی نوشت

• این مطلب فقط برای آدرس های آی پی نسخه ۴ قابل استفاده است.

موفق باشید 8)

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

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