• 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. MySQL Error “Too many connections” and how to resolve it
  2. What is the purpose of “system user” in MySQL Replication
  3. Understanding the Different Configuration files used for MySQL Server
  4. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  5. What are Reserved User Accounts in MySQL
  6. Understanding mysqlcheck and myisamchk utilities
  7. How To Configure Separate Override.conf For Multiple MySQL Instances Using Systemd
  8. How to Use External Python modules in MySQL Shell
  9. MySQL: How To Find Queries Taking Longer Than N Seconds
  10. How to Backup a MySQL Cluster

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright