MySQL: How to Store Multilingual & Special Character Data Using UTF-8

Every now and then, client requirements demand storing multilingual text, Arabic scripts, or special characters in a MySQL database. If the database encoding isn't configured properly, you end up with garbled text or question marks (???).

I ran into this exact issue recently on a client project. The fix is straightforward once you configure the right character set and collation.

Here is how to set up your tables and columns to handle multilingual data properly, either through a GUI or direct SQL queries.

Method 1: Using MySQL GUI (Workbench / phpMyAdmin)

1. Table Collation: Set the table collation to utf8 (or utf8mb4) with utf8_bin (or utf8mb4_bin).

2. Column Collation: Ensure each text column (VARCHAR, TEXT) is explicitly set to use utf8 character set and utf8_bin collation.

3. Save Changes: Apply the updates to your schema.

• [utf-8-table-setting.jpg] – Setting table-level collation

• [utf-8-to-column.jpg] – Setting column-level collation

• [data-in-table.jpg] – Multilingual data successfully stored inside the table

Method 2: Using SQL Queries

If you prefer running SQL scripts directly or need to alter existing tables, use the queries below.

Set Collation at Column and Table Creation

CREATE TABLE example_multilingual (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
    notes TEXT CHARACTER SET utf8 COLLATE utf8_bin
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

Alter Existing Columns

If your table already exists and you need to update existing columns to support special character sets:

 ALTER TABLE `admin_kairali`.`t_report_language` 
    CHANGE COLUMN `vchBeing` `vchBeing` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchScrtry` `vchScrtry` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchAccount` `vchAccount` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchReceiversign` `vchReceiversign` VARCHAR(4000) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL;

 

Quick Tip: For modern MySQL setups (MySQL 5.7+ and MySQL 8.0+), consider using utf8mb4 with utf8mb4_unicode_ci or utf8mb4_bin instead of standard utf8, as utf8mb4 fully supports 4-byte characters like emojis and complex scripts.