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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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 Configure Multiple MySQL Servers On One System Using mysqld_multi
  2. MySQL Backup stuck at “Starting to lock all the tables”
  3. Examples of mysqldump partial backups
  4. How to Generate Unique IDs For MySQL Cluster Backups
  5. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  6. MySQL 8.0 : Persisted Variables
  7. Beginners Guide to MySQL Data Types
  8. How to Change the Default Character Set and Collation for a Database in MySQL
  9. Granting All Privileges On All databases Except One Specific Table in MySQL
  10. How to Install MySQL Cluster on a single server for testing purposes

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary