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

دوال السلسلة النصية والتاريخ المتقدمة

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

دوال السلسلة النصية والتاريخ المتقدمة

معالجة السلاسل النصية والتاريخ هي مهارات أساسية لمعالجة البيانات وتحويلها. يوفر MySQL دوال قوية لتنظيف وتنسيق وتحليل البيانات النصية والزمنية. إتقان هذه الدوال سيحسن بشكل كبير قدراتك على معالجة البيانات.

دوال السلسلة النصية المتقدمة

SUBSTRING_INDEX() - تقسيم السلاسل النصية

تستخرج SUBSTRING_INDEX() أجزاء من السلاسل النصية بناءً على المحددات:

-- استخراج النطاق من البريد الإلكتروني
SELECT
    email,
    SUBSTRING_INDEX(email, '@', 1) AS username,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM customers;

-- استخراج الاسم الأول من الاسم الكامل
SELECT
    full_name,
    SUBSTRING_INDEX(full_name, ' ', 1) AS first_name,
    SUBSTRING_INDEX(full_name, ' ', -1) AS last_name
FROM employees;

-- استخراج النطاق الفرعي من URL
SELECT
    url,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '://', -1), '.', 1) AS subdomain,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '://', -1), '/', 1) AS domain
FROM websites;
-- مثال: https://shop.example.com/products
-- subdomain: shop
-- domain: shop.example.com

REPLACE() وتنظيف السلاسل النصية

-- تنظيف أرقام الهاتف
SELECT
    phone,
    REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', '') AS cleaned_phone
FROM contacts;

-- توحيد أكواد المنتجات
SELECT
    product_code,
    UPPER(REPLACE(TRIM(product_code), ' ', '-')) AS standardized_code
FROM products;

-- استبدالات متعددة باستخدام REPLACE المتداخلة
UPDATE products
SET description = REPLACE(
    REPLACE(
        REPLACE(description, '&', 'and'),
        '  ', ' '
    ),
    '\n\n', '\n'
)
WHERE description IS NOT NULL;

REGEXP - التعبيرات النمطية

يدعم MySQL التعبيرات النمطية لمطابقة الأنماط والاستخراج:

-- البحث عن رسائل البريد الإلكتروني بنمط معين
SELECT email
FROM customers
WHERE email REGEXP '^[a-z0-9]+@[a-z0-9]+\.[a-z]{2,}$';

-- البحث عن المنتجات بأكواد رقمية
SELECT product_name, product_code
FROM products
WHERE product_code REGEXP '^[A-Z]{2}[0-9]{4}$';
-- يطابق: AB1234, XY9999

-- استخراج الأرقام من النص
SELECT
    description,
    REGEXP_SUBSTR(description, '[0-9]+') AS first_number
FROM products;

دوال MySQL 8.0+: توفر REGEXP_REPLACE() و REGEXP_SUBSTR() و REGEXP_INSTR() عمليات regex متقدمة للبحث والاستبدال والاستخراج وإيجاد الموضع.

-- REGEXP_REPLACE: إزالة الأحرف غير الأبجدية الرقمية
SELECT
    product_name,
    REGEXP_REPLACE(product_name, '[^a-zA-Z0-9 ]', '') AS cleaned_name
FROM products;

-- REGEXP_SUBSTR: استخراج نمط معين
SELECT
    text,
    REGEXP_SUBSTR(text, '\b[A-Z]{2}[0-9]{3}\b') AS code
FROM documents;

معالجة السلسلة النصية المتقدمة

-- ملء السلاسل النصية إلى عرض ثابت
SELECT
    product_code,
    LPAD(product_code, 10, '0') AS padded_code,
    RPAD(customer_name, 30, '.') AS formatted_name
FROM orders;

-- عكس السلسلة النصية
SELECT
    REVERSE('MySQL') AS reversed;  -- LQSyM

-- توليد الأحرف الأولى
SELECT
    full_name,
    CONCAT(
        UPPER(SUBSTRING(SUBSTRING_INDEX(full_name, ' ', 1), 1, 1)),
        UPPER(SUBSTRING(SUBSTRING_INDEX(full_name, ' ', -1), 1, 1))
    ) AS initials
FROM employees;
-- John Smith → JS

دوال التاريخ والوقت المتقدمة

حسابات وعمليات التاريخ

-- حساب العمر من تاريخ الميلاد
SELECT
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age,
    TIMESTAMPDIFF(MONTH, birth_date, CURDATE()) AS age_in_months
FROM customers;

-- الأيام حتى/منذ الحدث
SELECT
    event_name,
    event_date,
    DATEDIFF(event_date, CURDATE()) AS days_until_event,
    CASE
        WHEN DATEDIFF(event_date, CURDATE()) < 0 THEN 'Past'
        WHEN DATEDIFF(event_date, CURDATE()) = 0 THEN 'Today'
        ELSE 'Future'
    END AS status
FROM events;

-- حساب أيام العمل (باستثناء عطلات نهاية الأسبوع)
SELECT
    order_date,
    delivery_date,
    DATEDIFF(delivery_date, order_date) AS total_days,
    DATEDIFF(delivery_date, order_date) -
    (WEEK(delivery_date) - WEEK(order_date)) * 2 AS business_days_approx
FROM orders;

-- إضافة أيام عمل (تخطي عطلات نهاية الأسبوع)
SELECT
    DATE_ADD(CURDATE(),
        INTERVAL 5 + (WEEKDAY(CURDATE() + INTERVAL 5 DAY) IN (5, 6)) * 2 DAY
    ) AS five_business_days_later;

DATE_FORMAT() - التنسيق المخصص

-- تنسيقات تاريخ متنوعة
SELECT
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_format,
    DATE_FORMAT(order_date, '%M %d, %Y') AS long_format,
    DATE_FORMAT(order_date, '%m/%d/%Y') AS us_format,
    DATE_FORMAT(order_date, '%d.%m.%Y') AS european_format,
    DATE_FORMAT(order_date, '%W, %M %D') AS day_month,
    DATE_FORMAT(order_date, '%Y-Q%q') AS quarter_format
FROM orders;

-- تنسيق: January 15, 2024
-- تنسيق: 01/15/2024
-- تنسيق: Monday, January 15th
-- تنسيق: 2024-Q1

-- تنسيق الوقت
SELECT
    created_at,
    DATE_FORMAT(created_at, '%H:%i:%s') AS time_24h,
    DATE_FORMAT(created_at, '%h:%i %p') AS time_12h,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS full_datetime
FROM logs;
-- 14:30:45 → 02:30 PM

STR_TO_DATE() - تحليل التواريخ

-- تحويل سلسلة نصية إلى تاريخ
SELECT
    STR_TO_DATE('15-01-2024', '%d-%m-%Y') AS parsed_date,
    STR_TO_DATE('January 15, 2024', '%M %d, %Y') AS parsed_date2,
    STR_TO_DATE('2024/01/15 14:30:00', '%Y/%m/%d %H:%i:%s') AS parsed_datetime;

-- استيراد البيانات بتنسيقات تاريخ متنوعة
UPDATE import_table
SET proper_date = STR_TO_DATE(date_string, '%m/%d/%Y')
WHERE date_string REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$';

تحويلات المنطقة الزمنية

-- التحويل بين المناطق الزمنية
SELECT
    event_time,
    CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS ny_time,
    CONVERT_TZ(event_time, 'UTC', 'Europe/London') AS london_time,
    CONVERT_TZ(event_time, 'UTC', 'Asia/Tokyo') AS tokyo_time
FROM events;

-- تخزين UTC، عرض في منطقة المستخدم الزمنية
SELECT
    created_at AS utc_time,
    CONVERT_TZ(created_at, '+00:00', user_timezone) AS local_time
FROM orders
JOIN users ON orders.user_id = users.user_id;

مهم: قم دائماً بتخزين التواريخ في UTC وقم بالتحويل إلى المناطق الزمنية المحلية فقط للعرض. استخدم أعمدة DATETIME أو TIMESTAMP، وليس VARCHAR، لتخزين التاريخ الصحيح.

أجزاء التاريخ والاستخراج

-- استخراج مكونات التاريخ
SELECT
    order_date,
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    DAY(order_date) AS day,
    QUARTER(order_date) AS quarter,
    WEEK(order_date) AS week_number,
    DAYOFWEEK(order_date) AS day_of_week,
    DAYNAME(order_date) AS day_name,
    MONTHNAME(order_date) AS month_name,
    LAST_DAY(order_date) AS last_day_of_month
FROM orders;

-- مكونات الوقت
SELECT
    created_at,
    HOUR(created_at) AS hour,
    MINUTE(created_at) AS minute,
    SECOND(created_at) AS second,
    TIME(created_at) AS time_only,
    DATE(created_at) AS date_only
FROM logs;

سيناريوهات التاريخ العملية

-- أول وآخر يوم في الشهر
SELECT
    CURDATE() AS today,
    DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day_of_month,
    LAST_DAY(CURDATE()) AS last_day_of_month,
    DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 DAY) AS last_day_prev_month;

-- نطاقات العمر للتحليلات
SELECT
    customer_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) < 18 THEN 'Under 18'
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 18 AND 24 THEN '18-24'
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 25 AND 34 THEN '25-34'
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 35 AND 44 THEN '35-44'
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 45 AND 54 THEN '45-54'
        ELSE '55+'
    END AS age_group
FROM customers;

-- حساب عمر العميل
SELECT
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    DATEDIFF(MAX(order_date), MIN(order_date)) AS customer_lifetime_days,
    TIMESTAMPDIFF(MONTH, MIN(order_date), MAX(order_date)) AS customer_lifetime_months,
    COUNT(*) AS total_orders,
    ROUND(COUNT(*) / GREATEST(TIMESTAMPDIFF(MONTH, MIN(order_date), MAX(order_date)), 1), 2) AS orders_per_month
FROM orders
GROUP BY customer_id;

دمج دوال السلسلة النصية والتاريخ

-- توليد أوصاف قابلة للقراءة
SELECT
    order_id,
    CONCAT(
        'Order #', order_id,
        ' placed by ', customer_name,
        ' on ', DATE_FORMAT(order_date, '%M %D, %Y'),
        ' (', DATEDIFF(CURDATE(), order_date), ' days ago)'
    ) AS order_description
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- توليد عناوين URL
SELECT
    title,
    LOWER(
        REPLACE(
            REPLACE(
                REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', ''),
                '  ', ' '
            ),
            ' ', '-'
        )
    ) AS slug
FROM blog_posts;
-- "My Amazing Product!" → "my-amazing-product"

مثال من العالم الحقيقي: خط أنابيب تنظيف البيانات

-- تنظيف شامل للبيانات
SELECT
    customer_id,
    -- تنظيف الاسم: قص، حالة العنوان
    CONCAT(
        UPPER(SUBSTRING(TRIM(first_name), 1, 1)),
        LOWER(SUBSTRING(TRIM(first_name), 2))
    ) AS cleaned_first_name,
    -- توحيد الهاتف
    CONCAT(
        '+1',
        REGEXP_REPLACE(phone, '[^0-9]', '')
    ) AS standardized_phone,
    -- التحقق من صحة البريد الإلكتروني وتنظيفه
    LOWER(TRIM(email)) AS cleaned_email,
    -- تحليل وتوحيد التاريخ
    STR_TO_DATE(birth_date_string, '%m/%d/%Y') AS birth_date,
    -- حساب العمر
    TIMESTAMPDIFF(YEAR, STR_TO_DATE(birth_date_string, '%m/%d/%Y'), CURDATE()) AS age
FROM raw_customer_data
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
  AND LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '')) = 10;

تمرين عملي:

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

استخراج اسم المستخدم والنطاق من جميع عناوين البريد الإلكتروني
البحث عن جميع الطلبات التي تم تقديمها في عطلات نهاية الأسبوع في العام الماضي
إنشاء تقرير يعرض مدة العميل بالسنوات والأشهر

الحلول:

-- 1. تحليل البريد الإلكتروني
SELECT
    email,
    SUBSTRING_INDEX(email, '@', 1) AS username,
    SUBSTRING_INDEX(email, '@', -1) AS domain,
    CONCAT(
        SUBSTRING(SUBSTRING_INDEX(email, '@', 1), 1, 2),
        '***@',
        SUBSTRING_INDEX(email, '@', -1)
    ) AS masked_email
FROM customers;

-- 2. طلبات عطلة نهاية الأسبوع
SELECT
    order_id,
    order_date,
    DAYNAME(order_date) AS day_name
FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7)  -- 1=الأحد، 7=السبت
  AND order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
ORDER BY order_date DESC;

-- 3. مدة العميل
SELECT
    customer_name,
    MIN(order_date) AS first_order,
    TIMESTAMPDIFF(YEAR, MIN(order_date), CURDATE()) AS years_with_us,
    MOD(TIMESTAMPDIFF(MONTH, MIN(order_date), CURDATE()), 12) AS additional_months,
    CONCAT(
        TIMESTAMPDIFF(YEAR, MIN(order_date), CURDATE()), ' years, ',
        MOD(TIMESTAMPDIFF(MONTH, MIN(order_date), CURDATE()), 12), ' months'
    ) AS tenure
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customer_name;

الملخص

في هذا الدرس، أتقنت:

SUBSTRING_INDEX() و REPLACE() و REGEXP لمعالجة السلسلة النصية
حسابات التاريخ باستخدام TIMESTAMPDIFF() و DATEDIFF()
DATE_FORMAT() و STR_TO_DATE() للتنسيق والتحليل
تحويلات المنطقة الزمنية باستخدام CONVERT_TZ()
استخراج مكونات التاريخ (YEAR، MONTH، DAY، إلخ.)
التطبيقات الواقعية في تنظيف البيانات وتحويلها

التالي: في الدرس الأخير من هذه الوحدة، سنتقن دوال التجميع و GROUP BY لإعداد التقارير والتحليلات المعقدة!