MySQL وتصميم قواعد البيانات

استيراد وتصدير البيانات

13 دقيقة الدرس 34 من 40

استيراد وتصدير البيانات

استيراد وتصدير البيانات ضروري لترحيل البيانات، والتكامل مع الأنظمة الخارجية، والعمليات الضخمة، ونقل البيانات بين البيئات. في هذا الدرس، ستتعلم طرقاً فعالة لاستيراد ملفات CSV، وتصدير نتائج الاستعلامات، ومعالجة مجموعات البيانات الكبيرة، وتقنيات تحويل البيانات.

لماذا الاستيراد/التصدير مهم

فهم حالات الاستخدام الشائعة يساعدك على اختيار الأداة المناسبة:

سيناريوهات الاستيراد/التصدير الشائعة:
✓ ترحيل البيانات من نظام قاعدة بيانات آخر
✓ تحميل البيانات بكميات كبيرة من ملفات CSV/Excel
✓ تصدير التقارير للتحليل في Excel/Google Sheets
✓ نقل البيانات بين التطوير والإنتاج
✓ التكامل مع واجهات برمجة التطبيقات الخارجية أو الأنظمة
✓ إنشاء أرشيفات البيانات
✓ تحميل بيانات الاختبار للتطوير
✓ مشاركة مجموعات البيانات مع الشركاء أو العملاء
✓ نسخ احتياطي للبيانات بتنسيق محمول

LOAD DATA INFILE - استيراد سريع بالجملة

LOAD DATA INFILE هي أسرع طريقة في MySQL لاستيراد مجموعات البيانات الكبيرة من ملفات نصية:

بناء جملة LOAD DATA INFILE الأساسي:
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- تخطي صف الرأس

مثال: استيراد المستخدمين من CSV
-- ملف CSV: users.csv
-- id,name,email,age
-- 1,John Doe,john@example.com,30
-- 2,Jane Smith,jane@example.com,25

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, age);

ملاحظة أمنية: بشكل افتراضي، تسمح MySQL فقط بتحميل الملفات من دليل secure_file_priv (عادةً /var/lib/mysql-files/). تحقق باستخدام: SHOW VARIABLES LIKE 'secure_file_priv';

خيارات LOAD DATA المتقدمة

التعامل مع تنسيقات الملفات المعقدة وتحويلات البيانات:

تحديد تعيين الأعمدة:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(product_name, @price_str, @qty_str, category)
SET
  price = CAST(@price_str AS DECIMAL(10,2)),
  quantity = CAST(@qty_str AS INT),
  created_at = NOW();

التعامل مع فواصل مختلفة:
-- قيم مفصولة بعلامة تبويب
LOAD DATA INFILE '/var/lib/mysql-files/data.tsv'
INTO TABLE data
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

-- ملف مفصول بعلامة الأنبوب
LOAD DATA INFILE '/var/lib/mysql-files/data.txt'
INTO TABLE data
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

التعامل مع نهايات أسطر Windows:
LOAD DATA INFILE '/var/lib/mysql-files/windows_file.csv'
INTO TABLE data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'  -- Windows CRLF
IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE - الاستيراد من العميل

تحميل الملفات من جهاز العميل بدلاً من الخادم:

تمكين LOCAL INFILE (إذا تم تعطيله):
-- في my.cnf:
[mysqld]
local_infile = 1

-- أو التمكين لكل جلسة:
SET GLOBAL local_infile = 1;

التحميل من كمبيوتر العميل:
LOAD DATA LOCAL INFILE '/home/user/Downloads/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

تحذير أمني: يمكن أن يكون LOAD DATA LOCAL INFILE خطراً أمنياً لأنه يسمح بقراءة أي ملف يمكن لمستخدم MySQL الوصول إليه. مكّنه فقط عند الضرورة وعطّله بعد الاستخدام.

SELECT INTO OUTFILE - تصدير البيانات

تصدير نتائج الاستعلام إلى ملف على الخادم:

التصدير الأساسي:
SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/users_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

التصدير مع رؤوس الأعمدة:
-- تصدير صف الرأس
SELECT 'id', 'name', 'email', 'created_at'
UNION ALL
SELECT id, name, email, created_at FROM users
INTO OUTFILE '/var/lib/mysql-files/users_with_headers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

تصدير أعمدة محددة:
SELECT user_id, username, email, DATE_FORMAT(created_at, '%Y-%m-%d') AS signup_date
FROM users
WHERE status = 'active'
INTO OUTFILE '/var/lib/mysql-files/active_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

نصيحة: سيفشل INTO OUTFILE إذا كان الملف موجوداً بالفعل. احذف الملف القديم أولاً أو استخدم طابعاً زمنياً في اسم الملف لإنشاء صادرات فريدة.

التصدير باستخدام mysqldump لترحيل البيانات

استخدم mysqldump للصادرات المحمولة والمستقلة عن قاعدة البيانات:

تصدير جداول محددة:
mysqldump -u root -p database_name table1 table2 > tables_export.sql

تصدير البيانات فقط (بدون CREATE TABLE):
mysqldump -u root -p --no-create-info database_name > data_only.sql

التصدير مع شرط WHERE:
mysqldump -u root -p database_name users \
  --where="created_at >= '2024-01-01'" > recent_users.sql

التصدير بصيغة CSV (وليس SQL):
mysqldump -u root -p database_name users \
  --tab=/var/lib/mysql-files/ \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"'
# ينشئ users.sql (البنية) و users.txt (البيانات)

التصدير إلى خادم بعيد:
mysqldump -u root -p database_name | \
  ssh remote_user@remote_server "mysql -u root -p remote_database"

استيراد ملفات CSV الكبيرة بكفاءة

تحسين الأداء عند استيراد ملايين الصفوف:

التحسين قبل الاستيراد:
-- تعطيل المفاتيح لتسريع الإدخال الضخم
ALTER TABLE large_table DISABLE KEYS;

-- استيراد البيانات
LOAD DATA INFILE '/var/lib/mysql-files/large_data.csv'
INTO TABLE large_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- إعادة تمكين المفاتيح وإعادة بناء الفهارس
ALTER TABLE large_table ENABLE KEYS;

تقسيم الملفات الكبيرة:
# تقسيم ملف 10 مليون صف إلى أجزاء مليون صف
split -l 1000000 large_file.csv chunk_

# استيراد كل جزء
for file in chunk_*; do
  mysql -u root -p -e "LOAD DATA INFILE '/var/lib/mysql-files/$file' \
    INTO TABLE data FIELDS TERMINATED BY ',' \
    LINES TERMINATED BY '\n';"
done

استخدام المعاملات لـ InnoDB:
START TRANSACTION;
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE innodb_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
COMMIT;

تحويل البيانات أثناء الاستيراد

تنظيف وتحويل البيانات أثناء الاستيراد:

تحويل أنواع البيانات:
LOAD DATA INFILE '/var/lib/mysql-files/sales.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@sale_date, @amount, product_name, @quantity)
SET
  sale_date = STR_TO_DATE(@sale_date, '%m/%d/%Y'),
  amount = CAST(REPLACE(@amount, '$', '') AS DECIMAL(10,2)),
  quantity = CAST(@quantity AS UNSIGNED),
  created_at = NOW();

التعامل مع قيم NULL:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, @description, @price)
SET
  description = NULLIF(@description, ''),
  price = IF(@price = '', NULL, @price);

إنشاء حقول محسوبة:
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(first_name, last_name, @email_prefix, salary)
SET
  email = CONCAT(@email_prefix, '@company.com'),
  full_name = CONCAT(first_name, ' ', last_name),
  monthly_salary = salary / 12;

تصدير البيانات عبر سطر الأوامر

تصدير البيانات مباشرة من الشل دون كتابة ملفات SQL:

التصدير إلى CSV مع عميل mysql:
mysql -u root -p -e "SELECT * FROM users" database_name \
  --batch \
  --skip-column-names \
  | sed 's/\t/,/g' > users.csv

التصدير مع الرؤوس:
mysql -u root -p database_name -e "SELECT * FROM users" \
  | sed 's/\t/,/g' > users_with_headers.csv

تصدير تقرير منسق:
mysql -u root -p -e "
  SELECT
    CONCAT(first_name, ' ', last_name) AS 'Full Name',
    email AS 'Email Address',
    DATE_FORMAT(created_at, '%Y-%m-%d') AS 'Signup Date'
  FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
" database_name > active_users_report.txt

تصدير تنسيق JSON:
mysql -u root -p database_name -e "
  SELECT JSON_OBJECT(
    'id', id,
    'name', name,
    'email', email
  ) AS json_data
  FROM users
" --batch --skip-column-names > users.json

التعامل مع أخطاء الاستيراد

التعامل مع مشكلات جودة البيانات أثناء الاستيراد:

تخطي الصفوف غير الصالحة:
LOAD DATA INFILE '/var/lib/mysql-files/dirty_data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, @price, quantity)
SET price = IF(@price REGEXP '^[0-9.]+$', @price, NULL);

تسجيل تحذيرات الاستيراد:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

-- التحقق من التحذيرات
SHOW WARNINGS;

-- عد الصفوف المتأثرة
SELECT ROW_COUNT();

استخدام REPLACE للتعامل مع التكرارات:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
REPLACE INTO TABLE products  -- تحديث إذا كان المفتاح الأساسي موجوداً
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

استخدام IGNORE لتخطي التكرارات:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
IGNORE INTO TABLE products  -- تخطي الصفوف مع مفاتيح مكررة
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

نقل البيانات عبر قواعد البيانات

نقل البيانات بين قواعد بيانات MySQL مختلفة أو خوادم:

نسخ جدول داخل نفس الخادم:
-- نسخ البنية والبيانات
CREATE TABLE new_db.users LIKE old_db.users;
INSERT INTO new_db.users SELECT * FROM old_db.users;

-- أو في خطوة واحدة
CREATE TABLE new_db.users AS SELECT * FROM old_db.users;

النقل بين الخوادم:
# التصدير من الخادم المصدر
mysqldump -h source_server -u user -p source_db table_name | \
  mysql -h dest_server -u user -p dest_db

نقل البيانات الانتقائي:
-- تصدير الطلبات الأخيرة فقط
mysqldump -h source_server -u root -p shop_db orders \
  --where="order_date >= '2024-01-01'" | \
  mysql -h dest_server -u root -p archive_db

مزامنة البيانات التزايدية:
INSERT INTO target_db.users
SELECT * FROM source_db.users
WHERE updated_at > (
  SELECT COALESCE(MAX(updated_at), '1970-01-01')
  FROM target_db.users
);

العمل مع ملفات Excel

استيراد وتصدير البيانات لمستخدمي Excel:

تصدير CSV متوافق مع Excel:
SELECT * FROM products
INTO OUTFILE '/var/lib/mysql-files/products_excel.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';  -- نهاية سطر Windows

الاستيراد من Excel CSV:
-- احفظ ملف Excel كـ CSV أولاً
LOAD DATA INFILE '/var/lib/mysql-files/excel_export.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

التعامل مع مشكلات ترميز Excel:
LOAD DATA INFILE '/var/lib/mysql-files/excel_utf8.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

نصيحة Excel: احفظ دائماً ملفات Excel كـ "CSV UTF-8" للحفاظ على الأحرف الخاصة. قد يفقد CSV القياسي اللكنات والأحرف غير ASCII.

نص الاستيراد/التصدير التلقائي

أنشئ نصاً قابلاً لإعادة الاستخدام لمهام الاستيراد/التصدير المنتظمة:

automated_export.sh:
#!/bin/bash

DB_NAME="shop_db"
DB_USER="export_user"
DB_PASS="ExportPass2024"
EXPORT_DIR="/var/exports/daily"
DATE=$(date +%Y%m%d)

# إنشاء دليل التصدير
mkdir -p $EXPORT_DIR

# تصدير المستخدمين
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
  SELECT * FROM users
  INTO OUTFILE '$EXPORT_DIR/users_$DATE.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
"

# تصدير الطلبات مع الربط
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
  SELECT
    o.order_id,
    u.email,
    o.total_amount,
    o.order_date
  FROM orders o
  JOIN users u ON o.user_id = u.id
  WHERE o.order_date = CURDATE()
  INTO OUTFILE '$EXPORT_DIR/daily_orders_$DATE.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
"

# ضغط الصادرات
gzip $EXPORT_DIR/*_$DATE.csv

echo "Export completed: $(date)"

مقارنة الأداء

فهم فروق الأداء يساعد في اختيار الأداة المناسبة:

مقارنة السرعة (مليون صف):

LOAD DATA INFILE:           ~5 ثوانٍ    ★★★★★
  الأسرع، مباشرة من الملف إلى الجدول

INSERT ... SELECT:          ~30 ثانية   ★★★★☆
  جيد لنقل نفس الخادم

mysqldump + mysql:          ~45 ثانية   ★★★☆☆
  محمول، جيد للترحيل

INSERT فردي:               ~5 دقائق    ★☆☆☆☆
  الأبطأ، استخدم فقط لمجموعات البيانات الصغيرة

التوصيات:
• < 1,000 صف: استخدم عبارات INSERT
• 1,000 - 100,000 صف: استخدم LOAD DATA أو mysqldump
• > 100,000 صف: استخدم دائماً LOAD DATA INFILE

تمرين عملي:

المهمة: استورد بيانات المنتجات، حوّلها، وصدّر تقريراً:

إنشاء جدول المنتجات
استيراد CSV مع تحويل السعر (إزالة علامة $)
حساب الأسعار المخفضة
تصدير المنتجات التي تزيد عن 100 دولار إلى CSV جديد

الحل:

-- 1. إنشاء الجدول
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  original_price DECIMAL(10,2),
  discount_percent INT,
  final_price DECIMAL(10,2),
  imported_at DATETIME
);

-- 2. الاستيراد مع التحويل
-- تنسيق CSV: name,price,discount
-- مثال: "Laptop","$1200",10
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, @price_str, discount_percent)
SET
  original_price = CAST(REPLACE(@price_str, '$', '') AS DECIMAL(10,2)),
  final_price = CAST(REPLACE(@price_str, '$', '') AS DECIMAL(10,2)) *
                (1 - discount_percent / 100),
  imported_at = NOW();

-- 3. التحقق من الاستيراد
SELECT name, original_price, discount_percent, final_price
FROM products
LIMIT 10;

-- 4. تصدير المنتجات المتميزة
SELECT name, original_price, discount_percent, final_price
FROM products
WHERE final_price > 100
ORDER BY final_price DESC
INTO OUTFILE '/var/lib/mysql-files/premium_products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

الملخص

في هذا الدرس، تعلمت:

LOAD DATA INFILE للاستيراد السريع بالجملة من ملفات CSV
SELECT INTO OUTFILE لتصدير نتائج الاستعلامات
خيارات متقدمة للتعامل مع تنسيقات الملفات المختلفة
تحويل البيانات أثناء الاستيراد (تحويل النوع، الحقول المحسوبة)
معالجة الأخطاء باستخدام REPLACE و IGNORE
تحسين الأداء لمجموعات البيانات الكبيرة
نقل البيانات عبر قواعد البيانات والخوادم
العمل مع التنسيقات المتوافقة مع Excel
نصوص الاستيراد/التصدير التلقائية
مقارنة أداء الطرق المختلفة

الوحدة مكتملة! لقد أكملت الوحدة 6: الأمان وإدارة المستخدمين. أنت الآن تعرف كيفية إدارة المستخدمين والصلاحيات، وتطبيق أفضل ممارسات أمان قاعدة البيانات، وإجراء النسخ الاحتياطي والاسترداد، واستيراد/تصدير البيانات بكفاءة. بعد ذلك، ستستكشف أنماط تصميم قاعدة البيانات في العالم الحقيقي في الوحدة 7!