Memperbarui himpunan karakter dan kolasi untuk database

Halaman ini menjelaskan cara memperbarui kumpulan karakter dan pengurutan database Cloud SQL.

Ringkasan

Saat membuat database di Cloud SQL untuk MySQL, Anda dapat menentukan konfigurasi set karakter dan pengurutan untuk database. Jika Anda tidak menentukan nilai kustom untuk setelan ini, database akan menggunakan nilai default. Di MySQL, Anda dapat menentukan nilai kustom untuk himpunan karakter dan kolasi di berbagai level objek, termasuk database, tabel, dan kolom. Untuk informasi selengkapnya tentang penggunaan konfigurasi himpunan karakter dan kolasi di MySQL, lihat Himpunan Karakter, Kolasi, Unicode.

Jika ingin mengubah himpunan karakter atau kolasi database setelah membuat database dan beberapa objek di database, Anda dapat melakukannya dengan mengikuti prosedur dalam dokumen ini.

Prosedur ini terdiri dari langkah-langkah berikut:

  1. Periksa nilai himpunan karakter dan kolasi saat ini.
  2. Tentukan tingkat update yang akan dilakukan.
  3. Buat cadangan instance.
  4. Buat perintah build ulang untuk objek database yang disimpan.
  5. Lakukan update pada database, dan jika perlu, tabel dan kolom.
  6. Validasi update dan cari error.
  7. Buat ulang objek database yang disimpan.
  8. Buat cadangan instance lain.

Memeriksa nilai himpunan karakter dan kolasi saat ini

Periksa nilai yang ada yang dikonfigurasi untuk himpunan karakter dan kolasi untuk database dan objek database Anda. Saat memeriksa database, Anda juga harus memeriksa konfigurasi tabel, kolom, dan objek yang disimpan. Anda dapat menggunakan pernyataan berikut untuk memeriksa database dan objek database-nya.

Memeriksa konfigurasi instance

Untuk memeriksa nilai global default di tingkat instance (server), mulai shell MySQL dan masukkan perintah berikut:

mysql> SELECT @@character_set_server,@@collation_server;

Contoh output:

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

Anda dapat memeriksa apakah nilai global default berbeda dengan nilai kustom yang akan Anda berikan untuk database nanti.

Memeriksa konfigurasi database

Untuk memeriksa nilai himpunan karakter dan kolasi yang ada di tingkat database, gunakan pernyataan berikut. Setelah menjalankan setiap pernyataan, buat daftar semua database dan objek database yang perlu Anda perbarui dengan set karakter atau nilai pengurutan baru. Daftar ini bergantung pada database yang ingin Andaubah dan jumlah tabel, kolom, dan objek database yang memerlukan update konfigurasi.

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

Misalnya, Anda akan menerima output berikut:

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| 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                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

Namun, Anda ingin mengubah database ke nilai kumpulan karakter kustom berikut:

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| 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                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

Kemudian, dalam hal ini, untuk setiap database yang memiliki nilai yang ingin diubah, Anda perlu memperbarui setiap objek yang berada dalam database.

Memeriksa konfigurasi tabel

Untuk memeriksa nilai yang ada untuk tabel, gunakan pernyataan berikut:

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');

Contoh output:

+------------+--------------+--------------------+-------------------+
| 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   |
+------------+--------------+--------------------+-------------------+

Memeriksa konfigurasi kolom tabel

Untuk memeriksa nilai kolom, gunakan pernyataan berikut:

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');

Contoh output:

+--------------+------------+-------------+--------------------+------------------+
| 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             |
+--------------+------------+-------------+--------------------+------------------+

Memeriksa skema atau database yang aktif

Untuk memeriksa detail skema atau database aktif, gunakan pernyataan berikut.

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;

Contoh output:

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

Memeriksa konfigurasi prosedur tersimpan

Untuk memeriksa nilai prosedur tersimpan, gunakan pernyataan berikut:

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

Ganti DB_NAME dengan nama database.

Contoh output:

+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| 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  |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

Untuk mendapatkan definisi prosedur tersimpan, gunakan pernyataan berikut:

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

Ganti kode berikut:

  • DB_NAME: nama database MySQL
  • SP_NAME: nama prosedur tersimpan

Jika prosedur tersimpan tidak menggunakan nilai himpunan karakter atau kolasi yang diinginkan, Anda mungkin perlu menghapus dan membuat ulang dengan himpunan karakter atau kolasi baru.

Memeriksa pemetaan pengurutan database

Pada contoh output sebelumnya, prosedur yang disimpan dibuat dengan latin1_swedish_ci sebagai pengurutan default dan set karakter yang sesuai adalah latin1. Artinya, semua pemrosesan terjadi menggunakan set karakter latin1 (kecuali jika parameter tertentu dari prosedur diberi tag dengan set karakter kustom.) Jika Anda hanya mengubah kumpulan karakter dan pengurutan tingkat database, update tingkat database tidak akan memperbarui prosedur yang disimpan secara otomatis. Prosedur ini terus beroperasi dengan set karakter latin1 dan pengurutan masing-masing. Menghapus dan membuat ulang prosedur tersimpan akan memastikan bahwa prosedur mulai menggunakan kumpulan karakter baru dan penggabungan baru database yang baru saja Anda perbarui.

Anda dapat mengetahui pemetaan antara kolasi database dan kumpulan karakter dengan membuat kueri tabel information_schema.COLLATION_CHARACTER_SET_APPLICABILITY.

Gunakan pernyataan berikut sebagai contoh:

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");

Contoh output:

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

Memeriksa konfigurasi pemicu

Untuk memeriksa nilai pemicu, gunakan pernyataan berikut sebagai contoh.

SHOW TRIGGERS FROM DB_NAME;

Ganti DB_NAME dengan nama database MySQL.

Untuk mendapatkan definisi pemicu, gunakan contoh pernyataan berikut.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

Ganti kode berikut:

  • DB_NAME: nama database MySQL
  • TRIGGER_NAME: nama pemicu

Jika pemicu tidak menggunakan nilai himpunan karakter atau kolasi yang diinginkan, Anda mungkin perlu menghapus dan membuat ulang pemicu tersebut dengan himpunan karakter atau kolasi baru. Pastikan pemicu menggunakan pemetaan pengurutan database yang benar. Untuk mengetahui informasi selengkapnya tentang cara memeriksa pengurutan database, lihat Memeriksa pemetaan pengurutan database.

Memeriksa konfigurasi peristiwa

Untuk memeriksa nilai peristiwa, gunakan pernyataan berikut sebagai contoh.

SHOW EVENTS FROM DB_NAME;

Ganti DB_NAME dengan nama database.

Untuk mendapatkan definisi peristiwa, gunakan contoh pernyataan berikut.

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

Ganti kode berikut:

  • DB_NAME: nama database
  • EVENT_NAME: nama peristiwa

Jika peristiwa tidak menggunakan nilai himpunan karakter atau kolasi yang diinginkan, Anda mungkin perlu menghapus dan membuat ulang peristiwa tersebut dengan himpunan karakter atau kolasi baru. Pastikan peristiwa menggunakan pemetaan pengurutan database yang benar. Untuk mengetahui informasi selengkapnya tentang cara memeriksa pengurutan database, lihat Memeriksa pemetaan pengurutan database.

Memeriksa konfigurasi fungsi Anda

Untuk memeriksa nilai fungsi, gunakan pernyataan berikut sebagai contoh.

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

Ganti DB_NAME dengan nama database.

Untuk mendapatkan definisi fungsi, gunakan contoh pernyataan berikut.

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

Ganti kode berikut:

  • DB_NAME: nama database MySQL
  • FUNCTION_NAME: nama fungsi

Jika fungsi tidak menggunakan nilai himpunan karakter atau kolasi yang diinginkan, Anda mungkin perlu menghapus dan membuat ulang fungsi tersebut dengan himpunan karakter atau kolasi baru. Pastikan fungsi menggunakan pemetaan pengurutan database yang benar. Untuk mengetahui informasi selengkapnya tentang cara memeriksa pengurutan database, lihat Memeriksa pemetaan pengurutan database.

Kumpulan karakter atau pemeriksaan. Pastikan fungsi menggunakan pemetaan pengurutan database yang benar. Untuk mengetahui informasi selengkapnya tentang cara memeriksa pengurutan database, lihat Memeriksa pemetaan pengurutan database.

Menentukan tingkat update yang akan dilakukan

Setelah memeriksa konfigurasi himpunan karakter dan pengumpulan data database, Anda memiliki daftar database dan mungkin daftar objek database yang perlu diperbarui.

Jumlah tugas yang perlu Anda lakukan bergantung pada jenis objek database yang memerlukan perubahan konfigurasi.

Mencadangkan instance

Sebelum melakukan pembaruan ini, buat cadangan instance Anda. Untuk mengetahui informasi selengkapnya, lihat Membuat cadangan sesuai permintaan. Sebaiknya Anda membuat cadangan tanpa operasi DDL yang berjalan di instance.

Membuat perintah rebuild untuk objek database yang disimpan di database Anda

Jika Anda perlu menghapus dan membuat ulang objek database yang disimpan di database dengan set karakter dan pengurutan baru, gunakan alat mysqldump untuk membuat perintah sekarang. Setelah memperbarui database dan kolom tabel, Anda akan menggunakan file mysqldump yang dihasilkan untuk membuat ulang objek database yang disimpan di database. Perintah ini menghasilkan pernyataan untuk semua prosedur tersimpan, fungsi, pemicu, dan peristiwa.

Jalankan perintah berikut agar Anda dapat menggunakan output untuk membuat ulang semua prosedur, fungsi, dan peristiwa yang disimpan dari database tertentu nanti (saat Anda membuat ulang objek database yang disimpan).

$ 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

Ganti kode berikut:

  • IP_ADDRESS: Alamat IP instance Cloud SQL untuk MySQL
  • DB_NAME: nama database MySQL

Lakukan update pada database, dan jika perlu, tabel dan kolom.

Pada langkah ini, Anda akan memperbarui himpunan karakter dan nilai pengurutan database. Selain itu, Anda mungkin perlu membuat ulang tabel dan kolom.

Melakukan update tingkat database

Jika Anda hanya melakukan update tingkat database, jalankan pernyataan berikut untuk mengupdate database:

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

Ganti kode berikut:

  • DB_NAME: nama database yang akan diperbarui
  • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke database
  • NEW_COLLATION: pengurutan baru yang akan diterapkan ke database

Melakukan update tingkat database dan tabel

Jika Anda melakukan update tingkat database dan tabel, lakukan hal berikut:

  1. Jalankan pernyataan berikut untuk memperbarui database Anda:

    ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    Ganti kode berikut:

    • DB_NAME: nama database yang akan diperbarui
    • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke database
    • NEW_COLLATION: pengurutan baru yang akan diterapkan ke database

  2. Untuk setiap tabel yang ingin Anda perbarui, jalankan pernyataan berikut:

    ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    Ganti kode berikut:

    • TABLE_NAME: nama tabel yang akan diperbarui
    • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
    • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel

Melakukan update tingkat database, tabel, dan kolom

Pada tingkat ini, Anda perlu mengenkode ulang data yang ada agar sesuai dengan setelan set karakter dan pengurutan baru. Jika Anda melakukan update database, tabel, dan tingkat kolom, lakukan hal berikut:

  1. Sebelum melanjutkan pembaruan pada kolom tabel, tinjau pertimbangan berikut:

    • Konversi kumpulan karakter dan pengurutan ALTER TABLE menyebabkan tabel dibuat ulang dan mengunci tabel. Akibatnya, setiap kueri aktif yang mencoba mengakses tabel akan diblokir.
    • Konversi kumpulan karakter dan pengurutan ALTER TABLE memerlukan waktu karena mengambil tabel lengkap dalam memori. Operasi ini dapat meningkatkan latensi beban kerja apa pun yang berjalan secara paralel karena halaman kumpulan buffer sedang digunakan untuk tabel yang diperbarui.
    • Jika ada kueri yang mengakses beberapa tabel dan beberapa tabel sedang diperbarui sementara yang lain belum diperbarui, kueri tersebut mungkin menghasilkan hasil yang tidak konsisten selama waktu transaksi ini.
    • Proses menjalankan konversi pengurutan dan kumpulan karakter ALTER TABLE akan membuat tabel bayangan, jadi rencanakan penggunaan disk dengan tepat.
    • Konversi kumpulan karakter dan pengurutan ALTER TABLE juga menyebabkan indeks apa pun di tabel dibuat ulang.
    • Anda tidak perlu mengupdate instance replika secara terpisah. Perintah ALTER, DROP, dan CREATE direplikasi secara otomatis. Namun, menjalankan perintah DDL dapat menyebabkan waktu jeda replika meningkat selama operasi.
    • Jika Anda memulihkan database dari cadangan yang diambil sebelum menerapkan update ini, Anda harus menerapkan ulang update.

  2. Periksa tabel Anda untuk menemukan tuple data yang mungkin tidak kompatibel dengan set karakter baru. Jika ada inkompatibilitas, Anda harus memperbaikinya sebelum mengupdate set karakter. Jika tidak, Anda akan mengalami error selama proses konversi. Anda dapat menggunakan pernyataan SELECT CONVERT berikut untuk memvalidasi data dengan kumpulan karakter baru.

    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);

    Contoh output:

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

    Dalam contoh ini, karena output menampilkan tiga tuple ini, ada tiga nilai yang tidak konsisten yang perlu diperbaiki. Sebelum melanjutkan ke langkah berikutnya, periksa output kueri, dan perbaiki data hingga kueri menampilkan nol tuple untuk semua kolom yang memerlukan konversi. Anda juga dapat menggabungkan beberapa kolom dalam kueri SELECT yang sama.

  3. Pilih opsi untuk memperbarui kumpulan karakter dan pengurutan untuk tabel Anda di tingkat kolom.

  4. Jika Anda ingin memperbarui kumpulan karakter untuk kolom tabel yang sangat spesifik, ikuti langkah-langkah di tab Tingkat kolom tempat Anda hanya perlu mengenkode ulang kolom yang dipilih. Jika tidak, ikuti langkah-langkah di tab Tingkat tabel yang mengenkode ulang tabel lengkap.

    Tingkat tabel

    1. Jalankan pernyataan berikut untuk memperbarui database Anda:

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ganti kode berikut:

      • DB_NAME: nama database yang akan diperbarui
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke database
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke database

    2. Untuk setiap tabel yang terpengaruh, jalankan pernyataan berikut untuk memperbarui tabel:

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ganti kode berikut:

      • TABLE_NAME: nama tabel yang akan diperbarui
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel

    3. Update di tingkat tabel sehingga semua kolom yang merupakan bagian dari tabel diperbarui dengan kumpulan karakter dan pengurutan baru. Opsi ini akan membangun ulang tabel sepenuhnya. Untuk menggunakan opsi ini, jalankan pernyataan berikut:

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

      Ganti kode berikut:

      • TABLE_NAME: nama tabel yang akan diperbarui
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel

    Tingkat kolom

    Memperbarui di tingkat kolom. Opsi ini juga akan membuat ulang tabel sepenuhnya. Jika beberapa kolom diupdate, opsi ini dapat menyebabkan beberapa proses build ulang.

    Untuk menggunakan opsi ini, jalankan pernyataan berikut:

    1. Jalankan pernyataan berikut untuk memperbarui database Anda:
      2. ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ganti kode berikut:

      • DB_NAME: nama database yang akan diperbarui
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke database
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke database
    2. Untuk setiap tabel yang terpengaruh, jalankan pernyataan berikut untuk memperbarui tabel:
      3. ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ganti kode berikut:

      • TABLE_NAME: nama tabel yang akan diperbarui
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel
    3. Jalankan pernyataan berikut untuk memperbarui di tingkat kolom:
      4. ALTER TABLE DB_NAME.TABLE_NAME
MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ganti kode berikut:

      • DB_NAME: nama database yang akan diperbarui
      • TABLE_NAME: nama tabel yang akan diperbarui
      • COLUMN_DEFINITION: menentukan definisi kolom yang tepat yang menyertakan batasan. Definisi kolom mungkin membawa nilai himpunan karakter dan kolasi lama. Anda harus memperbarui definisi kolom dengan nilai baru.
      • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke kolom tabel
      • NEW_COLLATION: pengurutan baru yang akan diterapkan ke kolom tabel

      Ulangi pernyataan ini untuk semua kolom string yang ada dalam tabel yang memerlukan pembaruan karakter atau pengurutan.

Memvalidasi update dan mencari error

Selama operasi ALTER TABLE, cari error yang menunjukkan bahwa data yang ada dalam tabel tidak kompatibel dengan kumpulan karakter baru. Misalnya:

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

Jika Anda mengalami error kompatibilitas data, sebaiknya Anda memeriksa tabel lagi untuk menemukan error kompatibilitas data dan memperbaikinya sebelum menjalankan kembali pernyataan ALTER TABLE untuk mengenkode ulang data.

Selain itu, selama operasi ALTER TABLE, tabel (tabel kunci asing dan tabel yang dirujuk), pemeriksaan kunci asing mungkin gagal saat kolom mengalami encoding ulang. Jika hal ini terjadi, Anda dapat menonaktifkan pemeriksaan kunci asing (SET FOREIGN_KEY_CHECKS=0;) dan menjalankan update lagi.

Membuat ulang objek database yang disimpan

Setelah berhasil memperbarui database, Anda dapat menggunakan file mysqldump yang dihasilkan untuk membuat ulang objek database yang disimpan dengan nilai himpunan karakter dan kolasi.

Untuk membuat ulang objek database yang disimpan, lakukan langkah berikut:

  1. Verifikasi bahwa objek database yang disimpan ada di database.
  2. Pastikan tidak ada beban paralel yang mengharapkan objek database yang disimpan ada selama waktu penghapusan dan pembuatan ulang ini.

  3. File mysqldump yang Anda buat dengan alat mysqldump tidak menyertakan pernyataan untuk menghapus pemicu. Jika Anda perlu menghapus pemicu, buka terminal MySQL untuk membuat pernyataan DROP untuk pemicu.

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

  4. Salin output.

  5. Jalankan pernyataan untuk menghapus pemicu untuk database Anda, baik di MySQL shell maupun dalam skrip.

  6. Setelah Anda meletakkan pemicu, terapkan file dump yang dibuat dengan alat mysqldump. Untuk menerapkan file dump dan membuat ulang objek, masukkan hal berikut:

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

    Ganti kode berikut:

    • IP_ADDRESS: Alamat IP instance Cloud SQL untuk MySQL
    • DB_NAME: nama database

Membuat cadangan instance lain

Setelah menyelesaikan update, buat cadangan instance Anda lagi. Untuk mengetahui informasi selengkapnya, lihat Membuat cadangan sesuai permintaan.

Contoh skrip

Jika Anda perlu memperbarui database, tabel, dan kolom, bagian ini menyediakan contoh skrip untuk membantu mengenkode ulang data Anda.

Skrip untuk memperbarui beberapa tabel

Skrip berikut menghasilkan pernyataan untuk semua tabel dalam database tertentu. Pernyataan ini memungkinkan Anda mengenkode ulang data yang ada agar sesuai dengan setelan set karakter dan pengurutan baru.

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';

Ganti kode berikut:

  • DB_NAME: nama database yang akan diperbarui
  • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
  • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel

Contoh output:

+--------------------------------------------------------------------------------------------+
|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>;       |
+--------------------------------------------------------------------------------------------+

Skrip untuk memperbarui beberapa kolom dalam tabel

Skrip berikut menghasilkan pernyataan untuk semua kolom dalam tabel tertentu. Pernyataan ini memungkinkan Anda mengenkode ulang data yang ada agar sesuai dengan setelan set karakter dan pengurutan baru.

  1. Membuat pernyataan ALTER TABLE untuk semua tabel dalam database

    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';

    Ganti kode berikut:

    • DB_NAME: nama database yang akan diperbarui
    • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke tabel
    • NEW_COLLATION: pengurutan baru yang akan diterapkan ke tabel

    Contoh output:

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

  2. Untuk setiap tabel, buat perintah 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');

    Ganti kode berikut:

    • DB_NAME: nama database yang akan diperbarui
    • TABLE_NAME: nama tabel yang akan diperbarui
    • COLUMN_DEFINITION: setelah mendapatkan hasil dari setiap kueri, ganti dengan definisi kolom untuk setiap kolom (definisi kolom dapat berbeda di setiap kolom)
    • NEW_CHARSET: kumpulan karakter baru yang akan diterapkan ke kolom tabel
    • NEW_COLLATION: pengurutan baru yang akan diterapkan ke kolom tabel

    Contoh output:

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

Langkah selanjutnya