עדכון של ערכת התווים וההשוואה של מסד נתונים

בדף הזה מוסבר איך לעדכן את מערכת התווים ואת אוסף הכללים (collation) של מסדי נתונים ב-Cloud SQL.

סקירה כללית

כשיוצרים מסד נתונים ב-Cloud SQL ל-MySQL, אפשר לציין את ההגדרה של ערכת התווים וההשוואה של מסד הנתונים. אם לא מציינים ערכים מותאמים אישית להגדרות האלה, מסד הנתונים משתמש בערך ברירת המחדל. ב-MySQL, אפשר לציין ערכים מותאמים אישית עבור ערכת התווים וההשוואה ברמות שונות של אובייקטים, כולל מסדי נתונים, טבלאות ועמודות. מידע נוסף על שימוש בהגדרות של מערכת תווים ואוסף כללים (collation) ב-MySQL זמין במאמר מערכות תווים, אוסף כללים (collation), Unicode.

אם רוצים לשנות את ערכת התווים או את כללי המיון של מסד הנתונים אחרי שכבר יצרתם את מסד הנתונים וכמה אובייקטים במסד הנתונים, אתם יכולים לעשות זאת באמצעות ההליך שמתואר במסמך הזה.

התהליך הזה כולל את השלבים הבאים:

  1. בדיקת ערכי ערכת התווים וההשוואה הנוכחיים
  2. בוחרים את רמת העדכון הרצויה.
  3. יצירת גיבוי של המופע
  4. יצירת פקודות לבנייה מחדש של אובייקטים מאוחסנים במסד הנתונים.
  5. מבצעים עדכונים במסדי הנתונים, ובמידת הצורך, בטבלאות ובעמודות.
  6. מאמתים את העדכונים ומחפשים שגיאות.
  7. יוצרים מחדש את אובייקטי מסד הנתונים המאוחסנים.
  8. יוצרים גיבוי נוסף של המופע.

בדיקת הערכים הנוכחיים של מערכת התווים ואוסף הכללים (collation)

בודקים את הערכים הקיימים שהוגדרו עבור ערכת התווים וההשוואה במסד הנתונים ובאובייקטים של מסד הנתונים. כשבודקים את מסד הנתונים, צריך לבדוק גם את ההגדרה של הטבלאות, העמודות והאובייקטים המאוחסנים שלו. אפשר להשתמש בהצהרות הבאות כדי לבדוק את מסדי הנתונים ואת אובייקטי מסד הנתונים.

בדיקת הגדרת המופע

כדי לבדוק את ערכי ברירת המחדל הגלובליים ברמת המכונה (השרת), מפעילים את MySQL Shell ומזינים את הפקודה הבאה:

mysql> SELECT @@character_set_server,@@collation_server;

פלט לדוגמה:

+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)

אפשר לבדוק שערכי ברירת המחדל הגלובליים שונים מהערכים המותאמים אישית שתספקו למסד הנתונים בהמשך.

בדיקת הגדרות מסד הנתונים

כדי לבדוק את ערכי מערכת התווים ואוסף הכללים הקיימים ברמת מסד הנתונים, משתמשים בהצהרה הבאה. אחרי שמריצים כל הצהרה, יוצרים רשימה של כל מסדי הנתונים ואובייקטים של מסדי הנתונים שצריך לעדכן עם ערכים חדשים של ערכות תווים או של כללי מיון. הרשימה תלויה במסדי הנתונים שרוצים לשנות ובמספר הטבלאות, העמודות ואובייקטים של מסד הנתונים שצריך לעדכן את ההגדרות שלהם.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name
NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');

לדוגמה, הפלט הבא מתקבל:

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test2       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test3       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test4       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

אבל אתם רוצים לשנות את מסדי הנתונים לערכים הבאים של קבוצת תווים בהתאמה אישית:

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | ascii                      | ascii_general_ci       |     NULL | NO                 |
| def          | test2       | latin1                     | latin1_swedish_ci      |     NULL | NO                 |
| def          | test3       | utf16                      | utf16_general_ci       |     NULL | NO                 |
| def          | test4       | ucs2                       | ucs2_general_ci        |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

במקרה כזה, לכל מסד נתונים שיש בו ערך שרוצים לשנות, צריך לעדכן כל אובייקט שנמצא במסד הנתונים.

בדיקת ההגדרות של הטבלאות

כדי לבדוק את הערכים הקיימים בטבלאות, משתמשים בהצהרה הבאה:

SELECT T.table_name, T.table_schema, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.`tables` T,
  information_schema.`collation_character_set_applicability` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema
NOT IN ('performance_schema', 'mysql', 'sys','information_schema');

פלט לדוגמה:

+------------+--------------+--------------------+-------------------+
| TABLE_NAME | TABLE_SCHEMA | CHARACTER_SET_NAME | COLLATION_NAME    |
+------------+--------------+--------------------+-------------------+
| t1         | test1        | ascii              | ascii_general_ci  |
| t2         | test2        | latin1             | latin1_swedish_ci |
| t3         | test3        | utf16              | utf16_general_ci  |
| t4         | test4        | ucs2               | ucs2_general_ci   |
+------------+--------------+--------------------+-------------------+

בדיקת ההגדרה של עמודות הטבלה

כדי לבדוק את הערכים של העמודות, משתמשים בהצהרה הבאה:

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

פלט לדוגמה:

‫
+--------------+------------+-------------+--------------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME   |
+--------------+------------+-------------+--------------------+------------------+
| test1        | t1         | c           | ascii              | ascii_general_ci |
| test1        | t1         | i           | NULL               | NULL             |
| test2        | t2         | j           | NULL               | NULL             |
| test2        | t2         | v           | ascii              | ascii_general_ci |
| test3        | t3         | k           | NULL               | NULL             |
| test3        | t3         | summary     | utf16              | utf16_general_ci |
| test4        | t4         | col         | ucs2               | ucs2_general_ci  |
| test4        | t4         | p           | NULL               | NULL             |
+--------------+------------+-------------+--------------------+------------------+

בדיקת הסכימה או מסד הנתונים הפעילים

כדי לבדוק את הפרטים של הסכימה או מסד הנתונים הפעילים, משתמשים בהצהרה הבאה.

SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext', 'set' )
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

פלט לדוגמה:

+------------+
| DATABASE() |
+------------+
| test3      |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3        | t3         | utf16            | summary     | text        | utf16              |
+--------------+------------+------------------+-------------+-------------+--------------------+

בדיקת ההגדרה של הפרוצדורות המאוחסנות

כדי לבדוק את הערכים של פרוצדורות מאוחסנות, משתמשים בהצהרה הבאה:

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

מחליפים את DB_NAME בשם של מסד הנתונים.

פלט לדוגמה:

+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db    | Name | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test2 | p1   | PROCEDURE | root@%  | 2024-08-09 11:47:05 | 2024-08-09 11:47:05 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| test2 | p2   | PROCEDURE | root@%  | 2024-08-09 11:48:36 | 2024-08-09 11:48:36 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

כדי לקבל את ההגדרה של פרוצדורה מאוחסנת, משתמשים בהצהרה הבאה:

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: השם של מסד הנתונים של MySQL
  • ‫SP_NAME: השם של התהליך המאוחסן

אם פרוצדורה מאוחסנת לא משתמשת בערך של ערכת התווים או ההשוואה שרציתם, יכול להיות שתצטרכו להסיר אותה וליצור אותה מחדש עם ערכת התווים או ההשוואה החדשה.

בדיקת מיפוי של איסוף נתונים במסד נתונים

בדוגמה הקודמת של הפלט, הפרוצדורה המאוחסנת נוצרת עם latin1_swedish_ci כאוסף כללים (collation) ברירת המחדל, וערכת התווים התואמת היא latin1. כלומר, כל העיבוד מתבצע באמצעות קבוצת התווים latin1 (אלא אם פרמטרים ספציפיים של ההליך מתויגים באמצעות קבוצת תווים מותאמת אישית). אם משנים רק את ערכת התווים ואת כללי המיון ברמת מסד הנתונים, העדכון ברמת מסד הנתונים לא מעדכן את הפרוצדורה המאוחסנת באופן אוטומטי. ההליך ממשיך לפעול עם ערכת התווים latin1 ואוסף הכללים (collation) המתאים. הסרה של הפרוצדורה המאוחסנת ויצירה שלה מחדש מבטיחות שהפרוצדורה תתחיל להשתמש בערכת התווים החדשה ובאוסף כללי המיון החדש של מסד הנתונים שעדכנתם.

כדי לגלות את המיפוי בין איסוף נתונים במסד הנתונים לבין ערכת התווים, אפשר להריץ שאילתה בטבלה information_schema.COLLATION_CHARACTER_SET_APPLICABILITY.

לדוגמה, אפשר להשתמש בהצהרה הבאה:

SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE COLLATION_NAME = (SELECT database_collation FROM information_schema.ROUTINES
                        WHERE ROUTINE_SCHEMA = "test2" and ROUTINE_NAME = "p1");

פלט לדוגמה:

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1             |
+--------------------+

בדיקת ההגדרות של הטריגרים

כדי לבדוק את הערכים של הטריגרים, אפשר להשתמש בהצהרה הבאה כדוגמה.

SHOW TRIGGERS FROM DB_NAME;

מחליפים את DB_NAME בשם של מסד הנתונים MySQL.

כדי לקבל את ההגדרה של טריגר, משתמשים בהצהרה לדוגמה הבאה.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: השם של מסד הנתונים של MySQL
  • ‫TRIGGER_NAME: שם הטריגר

אם הטריגר לא משתמש בערכת התווים או בערך ההשוואה הרצויים, יכול להיות שתצטרכו להסיר אותו וליצור אותו מחדש עם ערכת התווים או ההשוואה החדשה. בודקים שהטריגר משתמש במיפוי הנכון של איסוף נתונים ממסד הנתונים. מידע נוסף על בדיקת אוסף כללים (collation) של מסדי נתונים זמין במאמר בדיקת מיפוי השוואה של מסדי נתונים.

בדיקת ההגדרה של האירועים

כדי לבדוק את הערכים של אירועים, אפשר להשתמש בהצהרה הבאה כדוגמה.

SHOW EVENTS FROM DB_NAME;

מחליפים את DB_NAME בשם של מסד הנתונים.

כדי לקבל את ההגדרה של אירוע, משתמשים בהצהרה לדוגמה הבאה.

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: שם מסד הנתונים
  • ‫EVENT_NAME: שם האירוע

אם באירוע לא נעשה שימוש בערכת התווים או בערך ההשוואה הרצויים, יכול להיות שתצטרכו להסיר אותו וליצור אותו מחדש עם ערכת התווים או ההשוואה החדשה. בודקים שהאירוע משתמש במיפוי הנכון של אוסף כללים (collation) של מסד הנתונים. מידע נוסף על בדיקת השוואה (collation) של מסד נתונים זמין במאמר בדיקת מיפוי השוואה (collation) של מסד נתונים.

בדיקת ההגדרות של הפונקציות

כדי לבדוק את הערכים של פונקציות, אפשר להשתמש בהצהרה הבאה כדוגמה.

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

מחליפים את DB_NAME בשם של מסד הנתונים.

כדי לקבל את ההגדרה של פונקציה, משתמשים בהצהרה לדוגמה הבאה.

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: השם של מסד הנתונים של MySQL
  • ‫FUNCTION_NAME: השם של הפונקציה

אם הפונקציה לא משתמשת בערך של ערכת התווים או של כללי המיון שרציתם, יכול להיות שתצטרכו להסיר אותה וליצור אותה מחדש עם ערכת התווים או כללי המיון החדשים. בודקים שהפונקציה משתמשת במיפוי הנכון של כללי השוואה (collation) של מסד הנתונים. מידע נוסף על בדיקת איגום מסדי נתונים זמין במאמר בנושא בדיקת מיפוי איגום מסדי נתונים.

מערכת תווים או כללי מיון. בודקים שהפונקציה משתמשת במיפוי הנכון של כללי השוואה (collation) של מסד הנתונים. מידע נוסף על בדיקת איגום מסדי נתונים זמין במאמר בנושא בדיקת מיפוי איגום מסדי נתונים.

קביעת רמת העדכונים לביצוע

אחרי שבדקתם את הגדרות ערכת התווים וההשוואה של מסד הנתונים, יש לכם רשימה של מסדי נתונים ואולי גם רשימה של אובייקטים של מסדי נתונים שצריך לעדכן.

מספר המשימות שתצטרכו לבצע תלוי בסוג של אובייקטי מסד הנתונים שדורשים שינויים בהגדרות.

יצירת גיבוי של המופע

לפני שמבצעים את העדכונים האלה, צריך לגבות את המופע. מידע נוסף זמין במאמר יצירת גיבוי לפי דרישה. מומלץ לבצע את הגיבוי ללא הפעלת פעולות DDL במופע.

יצירת פקודות לבנייה מחדש של אובייקטים של מסד נתונים שמאוחסנים במסד הנתונים

אם אתם צריכים להסיר וליצור מחדש אובייקטים של מסד נתונים שמאוחסנים במסדי הנתונים שלכם עם ערכת התווים והכללים החדשים למיון, אתם יכולים להשתמש בכלי mysqldump כדי ליצור את הפקודות עכשיו. אחרי שמעדכנים את העמודות במסד הנתונים ובטבלה, משתמשים בקובץ mysqldump שנוצר כדי לבנות מחדש את אובייקטי מסד הנתונים המאוחסנים במסד הנתונים. הפקודה הזו יוצרת הצהרות לכל הפרוצדורות המאוחסנות, הפונקציות, הטריגרים והאירועים.

מריצים את הפקודה הבאה כדי שתוכלו להשתמש בפלט כדי ליצור מחדש את כל הפרוצדורות המאוחסנות, הפונקציות והאירועים של מסד נתונים מסוים מאוחר יותר (כשיוצרים מחדש אובייקטים מאוחסנים של מסד נתונים).

$ mysqldump -uDBAuser -p -h IP_ADDRESS \
   -P 3306 DB_NAME --no-data --no-create-db \
   --no-create-info --routines --triggers --events \
   --set-gtid-purged=OFF --events > dump_objects.sql

מחליפים את מה שכתוב בשדות הבאים:

  • ‫IP_ADDRESS: כתובת ה-IP של מכונת Cloud SQL ל-MySQL
  • ‫DB_NAME: השם של מסד הנתונים של MySQL

מבצעים עדכונים במסדי הנתונים, ובמידת הצורך בטבלאות ובעמודות.

בשלב הזה, מעדכנים את ערכי ערכת התווים ואוסף הכללים (collation) של מסדי הנתונים. בנוסף, יכול להיות שתצטרכו לבנות מחדש את הטבלאות והעמודות.

ביצוע עדכונים ברמת מסד הנתונים

אם אתם מבצעים עדכונים רק ברמת מסד הנתונים, אתם צריכים להריץ את ההצהרה הבאה כדי לעדכן את מסד הנתונים:

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
  • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על מסד הנתונים
  • ‫NEW_COLLATION: כללי המיון החדשים שרוצים להחיל על מסד הנתונים

ביצוע עדכונים ברמת מסד הנתונים והטבלה

אם אתם מבצעים עדכונים ברמת מסד הנתונים והטבלה, אתם צריכים לבצע את הפעולות הבאות:

  1. מריצים את ההצהרה הבאה כדי לעדכן את מסד הנתונים:

    ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
    • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על מסד הנתונים
    • ‫NEW_COLLATION: כללי המיון החדשים שרוצים להחיל על מסד הנתונים

  2. מריצים את ההצהרה הבאה לכל טבלה שרוצים לעדכן:

    ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
    • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
    • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה

ביצוע עדכונים ברמת מסד הנתונים, הטבלה והעמודה

ברמה הזו, צריך לקודד מחדש את הנתונים הקיימים כדי לעמוד בדרישות של מערכת התווים החדשה והגדרות אוסף הכללים. אם אתם מבצעים עדכונים ברמת מסד הנתונים, הטבלה והעמודה, אתם צריכים לבצע את הפעולות הבאות:

  1. לפני שממשיכים לעדכן את העמודות בטבלה, כדאי לעיין בשיקולים הבאים:

    • ההמרה של ערכת התווים ואוסף הכללים (collation) ALTER TABLE גורמת לבנייה מחדש של הטבלה ולנעילת הטבלה. כתוצאה מכך, כל שאילתה פעילה שמנסה לגשת לטבלה נחסמת.
    • ההמרה של ערכת התווים והמיון ALTER TABLE אורכת זמן כי היא מאחזרת את הטבלה המלאה בזיכרון. יכול להיות שהפעולה הזו תגדיל את זמן האחזור של עומס עבודה שפועל במקביל, כי הדפים של מאגר הנתונים הזמני משמשים לטבלה המעודכנת.
    • אם יש שאילתות שפונות למספר טבלאות וחלק מהטבלאות מתעדכנות בזמן שאחרות עדיין לא מתעדכנות, יכול להיות שהשאילתות יניבו תוצאות לא עקביות במהלך זמן העסקה הזה.
    • תהליך ההפעלה של המרת ALTER TABLE ערכת תווים ואוסף כללים (collation) יוצר טבלת צללים, לכן חשוב לתכנן את השימוש בדיסק בהתאם.
    • המרת ערכי ה-collation וערכת התווים ALTER TABLE גורמת גם לבנייה מחדש של כל האינדקסים בטבלה.
    • אין צורך לעדכן בנפרד את מופעי הרפליקה. הפקודות ALTER, DROP ו-CREATE משוכפלות אוטומטית. עם זאת, הפעלת פקודות DDL עלולה להגדיל את זמן ההשהיה של העותק במהלך הפעולה.
    • אם תשחזרו מסד נתונים מגיבוי שנוצר לפני שהחלתם את העדכונים האלה, תצטרכו להחיל את העדכונים מחדש.

  2. בודקים אם יש בטבלה טפלים של נתונים שלא תואמים לערכת התווים החדשה. אם יש אי-תאימויות, צריך לתקן אותן לפני שמעדכנים את ערכת התווים. אחרת, תקבלו שגיאות במהלך תהליך ההמרה. אפשר להשתמש בהצהרת SELECT CONVERT הבאה כדי לאמת את הנתונים באמצעות מערכת התווים החדשה.

    SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET)
FROM TABLE_NAME
WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);
     
    SELECT c,CONVERT(c USING 'ascii')
FROM t WHERE c != CONVERT(c USING ascii);

    פלט לדוגמה:

    +------+--------------------------+
| c    | convert(c using 'ascii') |
+------+--------------------------+
| é    | ?                        |
| é    | ?                        |
| £    | ?                        |
+------+--------------------------+

    בדוגמה הזו, הפלט מחזיר את שלושת הטפלים האלה, ולכן יש שלושה ערכים לא עקביים שצריך לתקן. לפני שעוברים לשלב הבא, בודקים את הפלט של השאילתה ומתקנים את הנתונים עד שהשאילתה מחזירה אפס טפלים לכל העמודות שצריך להמיר. אפשר גם לשלב כמה עמודות באותה שאילתה SELECT.

  3. בוחרים אפשרות לעדכון של מערכת התווים ואוסף הכללים (collation) של הטבלאות ברמת העמודה.

  4. אם רוצים לעדכן את ערכת התווים של עמודה ספציפית מאוד בטבלה, צריך לפעול לפי השלבים שבכרטיסייה 'ברמת העמודה', שבה צריך רק לקודד מחדש עמודות נבחרות. אחרת, פועלים לפי השלבים בכרטיסייה Table-level (ברמת הטבלה) כדי לקודד מחדש את הטבלה המלאה.

    ברמת הטבלה

    1. מריצים את ההצהרה הבאה כדי לעדכן את מסד הנתונים:

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על מסד הנתונים
      • ‫NEW_COLLATION: כללי המיון החדשים שרוצים להחיל על מסד הנתונים

    2. לכל טבלה מושפעת, מריצים את ההצהרה הבאה כדי לעדכן את הטבלה:

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
      • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה

    3. מעדכנים ברמת הטבלה כך שכל העמודות ששייכות לטבלה יעודכנו עם ערכת התווים והסדר החדשים. האפשרות הזו בונה מחדש את הטבלה באופן מלא. כדי להשתמש באפשרות הזו, מריצים את ההצהרה הבאה:

      ALTER TABLE DB_NAME.TABLE_NAME
CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
      • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה

    ברמת העמודה

    עדכון ברמת העמודה. האפשרות הזו גם בונה מחדש את הטבלה באופן מלא. אם מעדכנים כמה עמודות, האפשרות הזו עלולה לגרום לכמה בנייות מחדש.

    כדי להשתמש באפשרות הזו, מריצים את ההצהרה הבאה:

    1. מריצים את ההצהרה הבאה כדי לעדכן את מסד הנתונים:
      2. ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על מסד הנתונים
      • ‫NEW_COLLATION: כללי המיון החדשים שרוצים להחיל על מסד הנתונים
    2. לכל טבלה מושפעת, מריצים את ההצהרה הבאה כדי לעדכן את הטבלה:
      3. ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
      • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה
    3. מריצים את ההצהרה הבאה כדי לעדכן ברמת העמודה:
      4. ALTER TABLE DB_NAME.TABLE_NAME
MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      מחליפים את מה שכתוב בשדות הבאים:

      • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
      • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
      • ‫COLUMN_DEFINITION: מציינים הגדרה מדויקת של העמודה שכוללת אילוצים. הגדרת העמודה עשויה לכלול את ערכי מערכת התווים ואוסף הכללים הישנים. צריך לעדכן את הגדרת העמודה עם הערכים החדשים.
      • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על עמודת הטבלה
      • ‫NEW_COLLATION: אוסף הכללים (collation) החדשים שרוצים להחיל על עמודת הטבלה

      חוזרים על ההצהרה הזו לכל עמודות המחרוזות בטבלה שצריך לעדכן בהן את ערכת התווים או את כללי המיון.

מאמתים את העדכונים ומחפשים שגיאות

במהלך הפעולה ALTER TABLE, מחפשים שגיאות שמצביעות על כך שהנתונים הקיימים בטבלה לא תואמים לערכת התווים החדשה. לדוגמה:

ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1

אם נתקלתם בשגיאות תאימות נתונים, מומלץ לבדוק שוב את הטבלאות כדי לזהות שגיאות תאימות נתונים ולתקן אותן לפני שמריצים מחדש את הצהרות ALTER TABLE כדי לקודד מחדש את הנתונים.

בנוסף, במהלך הפעולה ALTER TABLE, יכול להיות שייכשל אימות של מפתח זר בטבלאות (הטבלה של המפתח הזר והטבלה שהמפתח הזר מפנה אליה) בזמן שהקידוד של העמודה משתנה. אם זה קורה, אפשר להשבית את הבדיקה של המפתח הזר (SET FOREIGN_KEY_CHECKS=0;) ולהפעיל את העדכון שוב.

יצירה מחדש של אובייקטים של מסד נתונים מאוחסן

אחרי שמעדכנים את מסד הנתונים בהצלחה, אפשר להשתמש בקובץ mysqldump שנוצר כדי ליצור מחדש את אובייקטי מסד הנתונים המאוחסנים עם ערכי מערכת התווים ואוסף הכללים (collation).

כדי ליצור מחדש את אובייקטי מסד הנתונים ששמרתם:

  1. מוודאים שאובייקטים של מסד הנתונים המאוחסן נמצאים במסד הנתונים.
  2. חשוב לוודא שאין טעינה מקבילה שמצפה שאובייקטים של מסד הנתונים המאוחסנים יהיו קיימים במהלך ההסרה והיצירה מחדש.

  3. קובץ mysqldump שנוצר באמצעות הכלי mysqldump לא כולל את ההצהרה לגבי הפסקת ההפעלה של טריגרים. אם צריך להשמיט טריגרים, פותחים טרמינל של MySQL כדי ליצור את הצהרת DROP לטריגרים.

    mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS
drop_trigger_statements FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DB_NAME;

  4. מעתיקים את הפלט.

  5. מריצים את ההצהרה כדי להסיר את הטריגרים ממסד הנתונים, באמצעות מעטפת MySQL או סקריפט.

  6. אחרי שמשחררים את הטריגרים, מחילים את קובץ ה-dump שנוצר באמצעות הכלי mysqldump. כדי להחיל את קובץ ה-dump וליצור מחדש את האובייקטים, מזינים את הפקודה הבאה:

    $ mysql -uDBAuser -p -h IP_ADDRESS \
  -P 3306 DB_NAME < dump_objects.sql

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫IP_ADDRESS: כתובת ה-IP של מכונת Cloud SQL ל-MySQL
    • ‫DB_NAME: שם מסד הנתונים

יצירת גיבוי נוסף של המופע

אחרי שמסיימים את העדכונים, יוצרים גיבוי נוסף של המופע. מידע נוסף זמין במאמר יצירת גיבוי לפי דרישה.

סקריפטים לדוגמה

אם אתם צריכים לעדכן מסדי נתונים, טבלאות ועמודות, תוכלו למצוא בקטע הזה סקריפטים לדוגמה שיעזרו לכם לקודד מחדש את הנתונים.

סקריפט לעדכון של כמה טבלאות

הסקריפט הבא יוצר הצהרה לכל הטבלאות במסד הנתונים הנתון. ההצהרה מאפשרת לקודד מחדש נתונים קיימים כדי לעמוד בדרישות של מערכת התווים החדשה והגדרות אוסף הכללים.

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ",
"ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ")
AS alter_sql FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
  • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
  • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה

פלט לדוגמה:

+--------------------------------------------------------------------------------------------+
|alter_sql
+--------------------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;
  ALTER TABLE test1.t1 CONVERT TO CHARACTER SET <new-charset> COLLATE <new-collation>;       |
+--------------------------------------------------------------------------------------------+

סקריפט לעדכון של כמה עמודות בטבלה

הסקריפט הבא יוצר הצהרה לכל העמודות בטבלה נתונה. ההצהרה מאפשרת לקודד מחדש נתונים קיימים כדי לעמוד בדרישות של מערכת התווים החדשה והגדרות אוסף הכללים.

  1. יצירת משפטי ALTER TABLE לכל הטבלאות במסד הנתונים

    SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ")
AS alter_table_statements
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
    • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על הטבלה
    • ‫NEW_COLLATION: אוסף הכללים (collation) החדש שיחול על הטבלה

    פלט לדוגמה:

    +------------------------------------------------------------------------------+
| alter_table_statements                                                       |
+------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;    |
+------------------------------------------------------------------------------+

  2. לכל טבלה, תפיק ALTER TABLE MODIFY פקודות.

    SELECT CONCAT( "ALTER TABLE ", table_name, " MODIFY ", column_name, 'COLUMN_DEFINITION' , " CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION ")
AS alter_table_column_statements FROM information_schema.columns
WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'
AND data_type IN ('char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext','set','enum');

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫DB_NAME: השם של מסד הנתונים שרוצים לעדכן
    • ‫TABLE_NAME: השם של הטבלה שרוצים לעדכן
    • ‫COLUMN_DEFINITION: אחרי קבלת התוצאות מכל שאילתה, צריך להחליף את ההגדרה של כל עמודה (ההגדרות של העמודות יכולות להיות שונות)
    • ‫NEW_CHARSET: ערכת התווים החדשה שרוצים להחיל על עמודת הטבלה
    • ‫NEW_COLLATION: אוסף הכללים (collation) החדשים שרוצים להחיל על עמודת הטבלה

    פלט לדוגמה:

    +-------------------------------------------------------------------------------------------------------------------------------------+
|alter_table_column_statements                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE t1 MODIFY c <column-definition-replaced-after-obtaining-result-set> CHARACTER SET <new-charset> COLLATE <new-collation>  |
+-------------------------------------------------------------------------------------------------------------------------------------+

המאמרים הבאים