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

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

סקירה כללית

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

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

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

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

בדיקת הערכים הנוכחיים של ערכת התווים וההשוואה

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

בדיקת ההגדרות של המכונה

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

mysql> SELECT @@character_set_server,@@collation_server;

פלט לדוגמה:

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

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

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

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

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 וההשוואה המתאימה. הסרה ויצירה מחדש של הפרוצדורה המאוחסנת מבטיחה שהפרוצדורה תתחיל להשתמש בערכת התווים החדשה ובאיסוף החדש של מסד הנתונים שעדכנתם.

כדי לברר את המיפוי בין אוסף כללים של מסד הנתונים לבין מערכת התווים, אפשר לשלוח שאילתה לטבלה 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: שם הטריגר

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

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

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

SHOW EVENTS FROM DB_NAME;

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

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

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

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

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

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

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

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

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

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

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

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

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

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

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

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

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

אחרי שבדקתם את ההגדרות של מערכת התווים ואוסף הכללים (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

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

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

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

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

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: שיטת הסידור החדשה שרוצים להחיל על הטבלה

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

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

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

    • ההמרה של מערכת התווים והאוסף כללים של ALTER TABLE גורמת לבנייה מחדש של הטבלה ולנעילה שלה. כתוצאה מכך, כל שאילתה פעילה שמנסה לגשת לטבלה נחסמת.
    • ההמרה של ערכת התווים וההשוואה של ALTER TABLE לוקחת זמן כי היא מאחזרת את הטבלה המלאה בזיכרון. הפעולה הזו עלולה להגדיל את זמן האחזור של כל עומס עבודה שפועל במקביל, כי הדפים של מאגר המאגרים משמשים לטבלה המעודכנת.
    • אם יש שאילתות שניגשות לכמה טבלאות, וחלק מהטבלאות מתעדכנות בזמן שאחרות עדיין לא, יכול להיות שהשאילתות יפיקו תוצאות לא עקביות במהלך זמן הטרנזקציה.
    • תהליך ההמרה של מערכת תווים ואיסוף נתונים של ALTER TABLE יוצר טבלת צל, לכן צריך לתכנן את השימוש בדיסק בהתאם.
    • ההמרה של מערכת התווים ואוסף הכללים של 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') |
+------+--------------------------+
| é    | ?                        |
| é    | ?                        |
| £    | ?                        |
+------+--------------------------+

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

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

  4. אם רוצים לעדכן את מערכת התווים של עמודה ספציפית בטבלה, פועלים לפי השלבים בכרטיסייה Column-level, שבה צריך לקודד מחדש רק את העמודות שנבחרו. אחרת, צריך לבצע את השלבים בכרטיסייה 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: שיטת הסידור החדשה שרוצים להחיל על הטבלה

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

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

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

      • ‫TABLE_NAME: שם הטבלה שרוצים לעדכן
      • ‫NEW_CHARSET: מערכת התווים החדשה שרוצים להחיל על הטבלה
      • ‫NEW_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: שיטת הסידור החדשה שרוצים להחיל על הטבלה
    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: שיטת הסידור החדשה שרוצים להחיל על עמודת הטבלה

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

אימות העדכונים ובדיקה אם יש שגיאות

במהלך הפעולה 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: שיטת הסידור החדשה שרוצים להחיל על הטבלה

פלט לדוגמה:

+--------------------------------------------------------------------------------------------+
|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: שיטת הסידור החדשה שרוצים להחיל על הטבלה

    פלט לדוגמה:

    +------------------------------------------------------------------------------+
| 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: שיטת הסידור החדשה שרוצים להחיל על עמודת הטבלה

    פלט לדוגמה:

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

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