• 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 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. Counting Rows Of A Table In MySQL Server
  2. Which Character Set Should Be Used To Store Emojis in MySQL Database
  3. How MySQL Enterprise Backup (MEB) uses locking while making a backup
  4. Changing the value of lower_case_table_names in MySQL 8
  5. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  6. How to Change Default Character Sets in MySQL using ALTER TABLE Statement
  7. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  8. Examples of mysqldump partial backups
  9. How to Troubleshoot InnoDB Lock Issues
  10. MySQL – How to undo (rollback) a set of SQL statements

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright