• 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 the Default Character Set and Collation for a Database in MySQL

By admin

You can specify the character set and collation to use by default for tables in a database.

Checking the current character set and collation

To determine the default character set and collation for a database, enter this SQL statement:

mysql> SHOW CREATE DATABASE database1;

*************************** 1. row ***************************
Database: database1
Create Database: CREATE DATABASE `database1`
/*!40100 DEFAULT CHARACTER SET latin1 */

You can also obtain this information from the INFORMATION_SCHEMA using a query such as this:

SELECT SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME='database1';

*************************** 1. row ***************************
SCHEMA_NAME: database1
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci

The database characteristics are stored in a db.opt file in the database directory on the file system. If the file exists, the contents will look something like this:

default-character-set=latin2
default-collation=latin2_bin

You can create or edit this file with a text editor, but it’s preferable to use the related SQL statements discussed in the next section.

Changing the character set and collation

When creating new databases, you would enter the CREATE DATABASE statement like this to set the default character set and collation:

CREATE DATABASE database1
DEFAULT CHARACTER SET latin2
DEFAULT COLLATE latin2_bin;

Notice that there are no commas between these options and this is one statement. To change the default settings for an existing database, enter an SQL statement like the following:

ALTER DATABASE database1
DEFAULT CHARACTER SET latin2
DEFAULT COLLATE latin2_bin;

Note that changing the default character set and collation for a database will have no effect on tables that already exist in that database. Also, the DEFAULT COLLATE of the database is ignored if a CHARACTER SET clause is supplied when creating tables in the database, even if the CHARACTER SET specified for the table is the same as the CHARACTER SET specified for the database. For example:

CREATE DATABASE database2
CHARACTER SET cp1257
COLLATE cp1257_bin;

CREATE TABLE database2.table1 (column1 VARCHAR(16))
CHARACTER SET cp1257;

SHOW CREATE TABLE database2.table1 \G

*************************** 1. row ***************************
Table: table1
Create Table: CREATE TABLE `table1` (
`column1` varchar(16) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1257

No COLLATE clause is specified in the output of SHOW CREATE TABLE because the default collation of the database is ignored when a COLLATE clause is specified explicitly in the CREATE TABLE statement.

Filed Under: mysql

Some more articles you might also be interested in …

  1. Which Character Set Should Be Used To Store Emojis in MySQL Database
  2. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  3. Understanding MySQL Pluggable Authentication
  4. What’s a good process to find and eliminate slow queries in MySQL
  5. MySQL : How To Find the Slowest Queries
  6. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  7. how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)
  8. How to Change Default Character Sets in MySQL using ALTER TABLE Statement
  9. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
  10. How to Migrate from Oracle to MySQL

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