Halaman ini menjelaskan cara mengupdate set karakter dan kolasi database Cloud SQL.
Ringkasan
Saat membuat database di Cloud SQL untuk MySQL, Anda dapat menentukan konfigurasi set karakter dan kolasi 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 tingkat objek yang berbeda, termasuk database, tabel, dan kolom. Untuk mengetahui informasi selengkapnya tentang penggunaan konfigurasi himpunan karakter dan kolase di MySQL, lihat Himpunan Karakter, Kolase, Unicode.
Jika Anda 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:
- Periksa nilai himpunan karakter dan kolasi saat ini.
- Tentukan tingkat update yang akan dilakukan.
- Buat cadangan instance.
- Membuat perintah pembangunan ulang untuk objek database tersimpan.
- Lakukan pembaruan pada database, dan jika perlu, tabel dan kolom.
- Validasi pembaruan dan cari error.
- Buat ulang objek database tersimpan Anda.
- Buat cadangan lain dari instance.
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 perlu memeriksa konfigurasi tabel, kolom, dan objek yang tersimpan. 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 dari nilai kustom yang akan Anda berikan untuk database nanti.
Periksa konfigurasi database Anda
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 kolasi baru. Daftar ini bergantung pada database yang ingin Anda ubah 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 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 set 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 Anda ubah, Anda perlu memperbarui setiap objek yang ada dalam database.
Periksa konfigurasi tabel Anda
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 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 |
+--------------+------------+------------------+-------------+-------------+--------------------+
Periksa konfigurasi prosedur tersimpan Anda
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 Anda inginkan, Anda mungkin perlu menghapus dan membuatnya ulang dengan himpunan karakter atau kolasi baru.
Memeriksa pemetaan kolasi database
Dalam output contoh sebelumnya, prosedur
tersimpan dibuat dengan latin1_swedish_ci sebagai kolasi 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 set karakter dan pengurutan tingkat database, maka update tingkat database tidak akan memperbarui prosedur tersimpan secara otomatis. Prosedur ini terus beroperasi dengan set karakter latin1 dan kolasi masing-masing.
Menghapus dan membuat ulang prosedur tersimpan akan memastikan bahwa prosedur mulai menggunakan set karakter baru dan kolasi baru dari database yang baru saja Anda perbarui.
Anda dapat mengetahui pemetaan antara kolasi database dan himpunan 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 kolasi atau set karakter yang Anda inginkan, Anda mungkin perlu menghapus dan membuat ulang pemicu tersebut dengan nilai kolasi atau set karakter yang baru. Pastikan pemicu menggunakan pemetaan kolasi database yang benar. Untuk mengetahui informasi selengkapnya tentang memeriksa pemetaan kolasi database, lihat Memeriksa pemetaan kolasi database.
Memeriksa konfigurasi acara Anda
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 kolasi atau set karakter yang Anda inginkan, Anda mungkin perlu menghapus dan membuat ulang peristiwa tersebut dengan kolasi atau set karakter baru. Pastikan peristiwa menggunakan pemetaan kolasi database yang benar. Untuk mengetahui informasi selengkapnya tentang memeriksa kolasi database, lihat Memeriksa pemetaan kolasi database.
Periksa 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 set karakter atau nilai kolasi yang Anda inginkan, maka Anda mungkin perlu menghapus dan membuat ulang fungsi tersebut dengan set karakter atau kolasi baru. Pastikan fungsi menggunakan pemetaan kolasi database yang benar. Untuk mengetahui informasi selengkapnya tentang memeriksa pemetaan kolasi database, lihat Memeriksa pemetaan kolasi database.
I set karakter atau pengurutan. Pastikan fungsi menggunakan pemetaan kolasi database yang benar. Untuk mengetahui informasi selengkapnya tentang memeriksa pemetaan kolasi database, lihat Memeriksa pemetaan kolasi database.
Menentukan tingkat update yang akan dilakukan
Setelah memeriksa konfigurasi himpunan karakter dan kolasi database, Anda akan memiliki daftar database dan mungkin daftar objek database yang perlu Anda perbarui.
Jumlah tugas yang harus Anda lakukan bergantung pada jenis objek database yang memerlukan perubahan konfigurasi.
- Melakukan update tingkat database. Anda hanya perlu memperbarui metadata MySQL.
- Melakukan pembaruan tingkat database dan tabel. Anda hanya perlu memperbarui metadata MySQL.
- Melakukan pembaruan tingkat database, tabel, dan kolom. Anda harus memperbarui metadata dan membangun ulang tabel serta objek database. Pembaruan ini dapat memakan waktu paling lama; namun, Anda dapat menggunakan salah satu skrip contoh yang disediakan untuk memperbarui semua tabel dan kolom dalam database tertentu.
Buat cadangan instance
Sebelum melakukan salah satu update ini, buat cadangan instance Anda. Untuk mengetahui informasi selengkapnya, lihat Membuat cadangan sesuai permintaan. Sebaiknya Anda membuat cadangan tanpa menjalankan operasi DDL apa pun di instance.
Buat perintah pembangunan ulang untuk objek database yang disimpan di database Anda
Jika Anda perlu menghapus dan membuat ulang objek database tersimpan di database dengan
set karakter dan kolasi baru, gunakan alat mysqldump untuk membuat
perintah sekarang. Setelah memperbarui kolom database dan tabel, Anda akan menggunakan file mysqldump yang dihasilkan untuk membangun kembali objek database yang disimpan di database Anda. 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 tersimpan dari database tertentu nanti (saat Anda membuat ulang objek database tersimpan).
$ 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 for MySQL
- DB_NAME: nama database MySQL
Lakukan pembaruan pada database Anda, dan jika perlu, tabel dan kolom.
Pada langkah ini, Anda akan memperbarui nilai himpunan karakter dan kolasi database. Selain itu, Anda mungkin perlu membangun ulang tabel dan kolom.
Melakukan update tingkat database
Jika Anda hanya melakukan update tingkat database, jalankan pernyataan berikut untuk mengupdate database Anda:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
- DB_NAME: nama database yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke database
- NEW_COLLATION: kolasi baru yang akan diterapkan ke database
Melakukan update tingkat database dan tabel
Jika Anda melakukan pembaruan tingkat database dan tabel, lakukan hal berikut:
Jalankan pernyataan berikut untuk mengupdate database Anda:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
- DB_NAME: nama database yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke database
- NEW_COLLATION: kolasi baru yang akan diterapkan ke database
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 diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi baru yang akan diterapkan ke tabel
Melakukan update tingkat database, tabel, dan kolom
Di tingkat ini, Anda perlu mengenkode ulang data yang ada agar sesuai dengan setelan set karakter dan kolasi yang baru. Jika Anda melakukan update tingkat database, tabel, dan kolom, lakukan hal berikut:
Sebelum melanjutkan pembaruan pada kolom tabel, tinjau pertimbangan berikut:
- Konversi set karakter dan pemeriksaan
ALTER TABLEmenyebabkan tabel dibangun ulang dan mengunci tabel. Akibatnya, kueri aktif yang mencoba mengakses tabel akan diblokir. - Konversi set karakter dan pemeriksaan
ALTER TABLEmemerlukan waktu karena mengambil tabel lengkap dalam memori. Operasi ini dapat meningkatkan latensi beban kerja yang berjalan secara paralel karena halaman kumpulan buffer digunakan untuk tabel yang diperbarui. - Jika ada kueri yang mengakses beberapa tabel dan beberapa tabel sedang diperbarui, sementara tabel lainnya belum diperbarui, maka kueri tersebut dapat menghasilkan hasil yang tidak konsisten selama waktu transaksi ini.
- Proses menjalankan konversi set karakter dan kolasi
ALTER TABLEmembuat tabel bayangan, jadi rencanakan penggunaan disk dengan tepat. - Konversi set karakter dan pemeriksaan
ALTER TABLEjuga menyebabkan indeks apa pun pada tabel dibuat ulang. - Anda tidak perlu memperbarui instance replika secara terpisah. Perintah
ALTER,DROP, danCREATEdireplikasi secara otomatis. Namun, menjalankan perintah DDL dapat menyebabkan waktu jeda replika meningkat selama operasi. - Jika Anda memulihkan salah satu database dari cadangan yang diambil sebelum Anda menerapkan pembaruan ini, Anda harus menerapkan kembali pembaruan tersebut.
- Konversi set karakter dan pemeriksaan
Periksa tabel Anda untuk menemukan tuple data yang mungkin tidak kompatibel dengan set karakter baru. Jika ada ketidakcocokan, Anda harus memperbaikinya sebelum mengupdate set karakter. Jika tidak, Anda akan mengalami error selama proses konversi. Anda dapat menggunakan pernyataan
SELECT CONVERTberikut untuk memvalidasi data dengan set 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 tidak konsisten yang perlu diperbaiki. Sebelum melanjutkan ke langkah berikutnya, periksa output kueri Anda, dan perbaiki data hingga kueri Anda menampilkan nol tuple untuk semua kolom yang perlu dikonversi. Anda juga dapat menggabungkan beberapa kolom dalam kueri
SELECTyang sama.Pilih opsi untuk memperbarui set karakter dan kolasi untuk tabel di tingkat kolom.
Jika Anda ingin memperbarui set 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 seluruh tabel.
Tingkat tabel
Jalankan pernyataan berikut untuk mengupdate database Anda:
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
- DB_NAME: nama database yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke database
- NEW_COLLATION: kolasi baru yang akan diterapkan ke database
Untuk setiap tabel yang terpengaruh, jalankan pernyataan berikut untuk memperbarui tabel Anda:
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
- TABLE_NAME: nama tabel yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi baru yang akan diterapkan ke tabel
Perbarui di tingkat tabel sehingga semua kolom yang merupakan bagian dari tabel diperbarui dengan set karakter dan kolasi 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 diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi baru yang akan diterapkan ke tabel
Tingkat kolom
Perbarui di tingkat kolom. Opsi ini juga membangun ulang tabel sepenuhnya. Jika beberapa kolom diperbarui, opsi ini dapat menyebabkan beberapa pembangunan ulang.
Untuk menggunakan opsi ini, jalankan pernyataan berikut:
- Jalankan pernyataan berikut untuk mengupdate database Anda:
- DB_NAME: nama database yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke database
- NEW_COLLATION: kolasi baru yang akan diterapkan ke database
- Untuk setiap tabel yang terpengaruh, jalankan pernyataan berikut untuk memperbarui tabel Anda:
- TABLE_NAME: nama tabel yang akan diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi baru yang akan diterapkan ke tabel
- Jalankan pernyataan berikut untuk memperbarui di tingkat kolom:
- DB_NAME: nama database yang akan diupdate
- TABLE_NAME: nama tabel yang akan diupdate
- COLUMN_DEFINITION: tentukan definisi kolom yang tepat yang mencakup batasan. Definisi kolom mungkin membawa nilai himpunan karakter dan kolasi lama. Anda harus memperbarui definisi kolom dengan nilai baru.
- NEW_CHARSET: set karakter baru yang akan diterapkan ke kolom tabel
- NEW_COLLATION: urutan kolase baru yang akan diterapkan ke kolom tabel
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
ALTER TABLE DB_NAME.TABLE_NAME MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Ganti kode berikut:
Ulangi pernyataan ini untuk semua kolom string yang ada dalam tabel yang memerlukan update set karakter atau kolasi.
Validasi update dan cari error
Selama operasi ALTER TABLE, cari error yang menunjukkan bahwa data yang ada di tabel tidak kompatibel dengan set karakter baru. Misalnya:
ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1
Jika Anda mengalami error kompatibilitas data, sebaiknya periksa kembali tabel untuk menemukan error kompatibilitas data dan perbaiki 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 menjalani pengodean ulang. Jika hal ini terjadi, Anda dapat menonaktifkan pemeriksaan
kunci asing (SET FOREIGN_KEY_CHECKS=0;) dan menjalankan update lagi.
Membuat ulang objek database tersimpan
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 tersimpan, lakukan langkah berikut:
- Verifikasi bahwa objek database yang disimpan ada di database.
- Pastikan tidak ada beban paralel yang mengharapkan objek database tersimpan ada selama waktu penghapusan dan pembuatan ulang ini.
File
mysqldumpyang Anda buat dengan alatmysqldumptidak menyertakan pernyataan untuk menghilangkan pemicu. Jika Anda perlu menghapus pemicu, buka terminal MySQL untuk membuat pernyataanDROPuntuk pemicu.mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS drop_trigger_statements FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = DB_NAME;
Salin output.
Jalankan pernyataan untuk menghapus pemicu database Anda, baik di shell MySQL maupun di skrip.
Setelah Anda melepaskan pemicu, terapkan file dump yang Anda buat dengan alat
mysqldump. Untuk menerapkan file dump dan membuat ulang objek, masukkan perintah berikut:$ mysql -uDBAuser -p -h IP_ADDRESS \ -P 3306 DB_NAME < dump_objects.sql
Ganti kode berikut:
- IP_ADDRESS: Alamat IP instance Cloud SQL for MySQL
- DB_NAME: nama database
Buat cadangan instance lain
Setelah Anda menyelesaikan pembaruan, buat cadangan lain dari instance Anda. 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 yang diberikan. Pernyataan ini memungkinkan Anda mengenkode ulang data yang ada agar sesuai dengan setelan karakter dan kolasi 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 diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi 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 karakter dan kolasi baru.
Membuat pernyataan
ALTER TABLEuntuk semua tabel dalam databaseSELECT 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 diupdate
- NEW_CHARSET: set karakter baru yang akan diterapkan ke tabel
- NEW_COLLATION: kolasi baru yang akan diterapkan ke tabel
Contoh output:
+------------------------------------------------------------------------------+ | alter_table_statements | +------------------------------------------------------------------------------+ | ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>; | +------------------------------------------------------------------------------+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 diupdate
- TABLE_NAME: nama tabel yang akan diupdate
- COLUMN_DEFINITION: setelah mendapatkan hasil dari setiap kueri, ganti dengan definisi kolom untuk setiap kolom (definisi kolom dapat berbeda di seluruh kolom)
- NEW_CHARSET: set karakter baru yang akan diterapkan ke kolom tabel
- NEW_COLLATION: urutan kolase 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> | +-------------------------------------------------------------------------------------------------------------------------------------+