• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Change Default Character Sets in MySQL using ALTER TABLE Statement

by admin

To put it simply, there are two ways you can alter the table to use a new character set.

1. ALTER TABLE tablename DEFAULT CHARACTER SET utf8;

This will alter the table to use the new character set as the default, but as a safety mechanism, it will only change the table definition for the default character set. That is, existing character fields will have the old character set per column. For example:

mysql> create table mybig5 (id int not null auto_increment primary key,      
    -> subject varchar(100) ) engine=innodb default charset big5;
Query OK, 0 rows affected (0.81 sec)
mysql> show create table mybig5;
+--------+--------------------------------+
| Table  | Create Table                   |
+--------+--------------------------------+
| mybig5 | CREATE TABLE `mybig5` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=big5      |
+--------+--------------------------------+
1 row in set (0.00 sec)
mysql> alter table mybig5 default charset utf8;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

Inserting a multi-byte string that worked in big5 character set, such as:

mysql> INSERT INTO mybig5 VALUES (NULL, UNHEX('E7BB8FE79086'));

 01:08:19  [INSERT - 0 row(s), 0.000 secs]  [Error Code: 1366, SQL State: HY000]  Incorrect string value: '\xE7\xBB\x8F\xE7\x90\x86' for column 'SUBJECT' at row 1 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]
mysql> show create table mybig5;
+--------+------------------------------------------------+
| Table  | Create Table                                   |
+--------+------------------------------------------------+
| mybig5 | CREATE TABLE `mybig5` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(100) CHARACTER SET big5 DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                      |
+--------+------------------------------------------------+
1 row in set (0.00 sec)

Notice that the ‘subject’ column has the original character set definition and when data is inserted, can result in the error above if the character sets do not match.

2. ALTER TABLE tablename CONVERT TO CHARACTER SET utf8;

This will change all the columns to the new character set and change the table as well. So you will end up with the required definition:

mysql> show create table mybig5;
+--------+--------------------------------+
| Table  | Create Table                   |
+--------+--------------------------------+
| mybig5 | CREATE TABLE `mybig5` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8      |
+--------+--------------------------------+
1 row in set (0.00 sec)

So if you see the incorrect string error on a table, check that the columns are not under a different character set to the default. Look at using the CONVERT clause to avoid the issue, but also be aware that certain tables may actually require different character sets for different columns.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to use mysqldump without CREATE TABLE statements
  2. How To Reset MySQL 8.0 Root Password On Windows
  3. How to Rollback RPM Upgrade of the MySQL Server
  4. What’s a good process to find and eliminate slow queries in MySQL
  5. Beginners Guide to Storage Engines in MySQL
  6. MySQL – How to Set Maximum Rates for Connections and Queries
  7. mysqldump – How to Restore a Specific Database From a Backup of All Databases
  8. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  9. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  10. How to List Users and Privileges in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright