Set column charset in MySQL?

MySQLMySQLi Database

Set column charset using character SET command. The syntax is as follows −

ALTER TABLE yourTableName MODIFY youColumName type CHARACTER SET anyCharcaterSetName;

You can use character set name utf8 or something elsE. To set column charset, let us first create a table. The query to create a table is as follows −

mysql> create table setCharsetDemo
   −> (
   −> FirstName varchar(60)
   −> );
Query OK, 0 rows affected (2.09 sec)

Now you can check the current column character set with the help of show command. The query is as follows −

mysql> show create table setCharsetDemo;

The following is the output −

+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                           |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| setCharsetDemo | CREATE TABLE `setcharsetdemo` (`FirstName` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now you can look at the above sample output. The default charset is utf8mb4. Let us set the charset to be other than utf8mb4.

We will set the character set to utf8. The query is as follows −

mysql> alter table setCharsetDemo modify FirstName varchar(60) CHARACTER SET utf8;
Query OK, 0 rows affected, 1 warning (2.81 sec)
Records: 0 Duplicates: 0 Warnings: 1

Let us check the character set have been changed or not. The query is as follows −

mysql> show create table setCharsetDemo;

The following output displays that we have successfully updated the charset −

+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                          |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| setCharsetDemo | CREATE TABLE `setcharsetdemo` (`FirstName` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
raja
Published on 16-Jan-2019 07:45:45
Advertisements