We encountered collation issues in our project. Subsequently, we gained an understanding of collation and applied it to specific columns with data types such as varchar or text.
Since it only requires changes for characters, this proves highly beneficial for all involved. Please make use of it.
How can we set collation for the string data type columns of the table?
When creating a table with string data type columns, it's advisable to specify the data type as 'VARCHAR' or 'TEXT' and set the collation to ‘utf8mb4_unicode_ci’.
Table:
CREATE TABLE mytable ( column1 VARCHAR(50) COLLATE utf8mb4_unicode_ci, column2 VARCHAR(50) COLLATE utf8mb4_unicode_ci,column3 int(12) );
Is it sufficient to insert/update bilingual text in the column?
Not only that, but we also need to establish collation settings at both the table and database levels. This is essential since the database collation is typically configured as latin1_swedish_ci/utf8mb4_general_ci or some other variant. Consequently, you must configure the database as outlined below.
Database:
CREATE DATABASE new_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Table:
CREATE TABLE mytable ( column1 VARCHAR(50) COLLATE utf8mb4_unicode_ci, column2 VARCHAR(50) COLLATE utf8mb4_unicode_ci);
Before setting the collation, please follow the example query provided above to apply it to your database.
How can procedure parameters be created with collation?
Typically, we create procedures with parameters as shown below (Standard Procedure). However, if we require multilanguage insertion, updating, or searching support, we need to use parameters with specified character sets and collations (Bilingual Procedure).
Standard Procedure:
DELIMITER //
CREATE PROCEDURE myProcedure (IN myParam VARCHAR(255) )
BEGIN
-- Procedure logic here
SELECT * FROM my_table WHERE my_column = myParam;
END//
DELIMITER ;
Bilingual Procedure:
DELIMITER //
CREATE PROCEDURE myProcedure (IN myParam VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci)
BEGIN
-- Procedure logic here
SELECT * FROM my_table WHERE my_column = myParam;
END//
DELIMITER ;