۱۳۸۹/۰۸/۰۳

وارد کردن اطلاعات از فایل به پایگاه داده MySQL


سلام

قبلا در مورد انتقال اطلاعات در MySQL توضیح دادم. مطلب امروز در زمینه وارد کردن اطلاعات (import) از یک فایل متنی (txt, csv و ...) به پایگاه داده MySQL می باشد. برای وارد کردن اطلاعات از یک فایل به پایگاه داده MySQL از دستور `LOAD DATA INFILE` استفاده می کنیم. شکل کلی این دستور به صورت زیر است:
[sql]
mysql> LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
[/sql]
ساده ترین شکل استفاده از این دستور به شکل زیر می باشد:
[sql]mysql> LOAD DATA INFILE 'data.txt' INTO TABLE my_db.my_table;
[/sql]
در صورتی که جداکننده فیلد ها (FIELDS TERMINATED) را مشخص نکنید، MySQL مقدار پیش فرض \t که همان تب (Tab) هست را در نظر می گیرد. همچنین مقدار پیش فرض جداکننده خطوط \n که همان خط جدید (New Line) می باشد را در نظر می گیرد.
نکته
در صورتی که فایل شما دارای کاراکترهای UTF8 می باشد، علاوه بر تعریف جدول my_table با encoding مربوط به UTF8، باید نوع encoding را نیز در دستور LOAD DATE بنویسید. به صورت:
[sql]LOAD DATA INFILE 'data.txt' INTO TABLE my_db.my_table character set UTF8;
[/sql]
در ادامه به بررسی یک مثال عملی در مورد واردکردن اطلاعات یک فایل CSV می پردازیم.

بررسی یک مثال عملی


فرض کنید فایلی به نام ip-list.csv با محتوای زیر دارید:

"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia"
"1.0.8.0","1.0.15.255","16779264","16781311","CN","China"
"1.0.16.0","1.0.31.255","16781312","16785407","JP","Japan"
"1.0.32.0","1.0.63.255","16785408","16793599","CN","China"
"1.0.64.0","1.0.127.255","16793600","16809983","JP","Japan"
"1.0.128.0","1.0.255.255","16809984","16842751","TH","Thailand"
"1.1.0.0","1.1.0.255","16842752","16843007","CN","China"
"1.1.1.0","1.1.1.255","16843008","16843263","AU","Australia"
"1.1.2.0","1.1.63.255","16843264","16859135","CN","China"
"1.1.64.0","1.1.127.255","16859136","16875519","JP","Japan"

این فایل تعدادی آی‌پی و کشور متناظر با آن‌ها را نمایش می دهد. برای وارد کردن این اطلاعات به پایگاه داده MySQL ابتدا باید جدولی مناسب با این داده‌ها بسازیم. برای آشنایی با انتخاب نوع مناسب داده می توانید به مطلب انواع داده در MySQL مراجعه نمایید. دستور زیر یک جدول به نام geoip ایجاد می کند:
[sql]
mysql> CREATE TABLE `geoip` (
`begin_ip` varchar(15) NOT NULL,
`end_ip` varchar(15) NOT NULL,
`begin_num` int(11) unsigned NOT NULL,
`end_num` int(11) unsigned NOT NULL,
`country` char(2) NOT NULL,
`name` varchar(50) NOT NULL
) ENGINE=MyISAM;
[/sql]

دستور زیر این اطلاعات را از فایل ip-list.csv به جدول geoip وارد می‌کند. در صورت نیاز باید آدرس صحیح فایل را نیز در کنار نام آن درج کنید.
[sql]
mysql> LOAD DATA INFILE 'ip-list.csv'
INTO TABLE geoip
FIELDS TERMINATED BY "," ENCLOSED BY '"';

Query OK, 12 rows affected (0.23 sec)
Records: 12 Deleted: 0 Skipped: 0 Warnings: 0
[/sql]
در دستور بالا مشخص کرده ایم که ستون‌ها با "," جدا شده و محتوای هر ستون نیز در داخل دو " قرار دارد.

منابع




پی نوشت
  • متاسفانه بخش توسعه پایگاه داده MySQL با استناد به قانون جرايم رايانه ای فیلتر شده است. امیدوارم مسئولین تفکر و تعقل بیشتری نسبت به فیلتر کردن سایت ها داشته باشند.

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



شاد باشید

۵ نظر:

  1. منم قبلا درباره این نوشتم : http://cyberrabbits.net/258/exportcsv-to-mysql/

    و کلا این قضیه قوانین ..... خیلی مسخرست. خیلی کم پیش میاد که اشتباهاتشون رو اصلاح کنن.

    پاسخحذف
  2. سلام
    چگونه می توان تنها یک ستون اکسل را به یک ستون از جدول mysql واردات کرد؟
    lol
    مشکل فارسی بودن ستونها در CSV رو چطور حل کنیم ؟ که ستونها جابه جا نشوند؟

    پاسخحذف
  3. سلام
    ببخشید من برای درس پایگاه داده باید دیاگرام بیمارستان رو دربیارم اما دقیقانمی تونم درست دربیارم می تونید کمکم کنید؟

    پاسخحذف
  4. سلام؛
    در کدوم بخش طراحی مشکل دارید؟ تا الان چه مقدار از کار رو انجام دادین؟

    پاسخحذف