search
HomeDatabaseMysql TutorialHow to set character set in mysql

How to set character set in mysql

May 27, 2023 pm 01:38 PM
mysql

1. The concept of character set

Character set standardizes the mapping relationship between codes and characters, and specifies the encoding method of computer characters. The character set settings in different database systems may be different, and different character sets have unique characteristics and advantages. Compared with Latin1/Latin7, GB2312/GBK/GB18030 has greater advantages in processing Chinese characters.

In MySQL, character set refers to the way to encode character data, including databases, tables and columns. In layman's terms, a character set refers to the way used to store and process characters and strings.

The character sets supported in MySQL mainly include the following: ASCII, GBK, GB2312, UTF-8, UTF-16 and ISO-8859, etc. Among them, UTF-8 is the most commonly used character set in MySQL because It supports multiple languages, has flexible encoding formats, saves space and other benefits.

2. How to set the MySQL character set

  1. Query the character set supported by MySQL

In the MySQL database, you can Query the character sets supported by the database through commands as follows:

SHOW CHARACTER SET;

This command can list the various character sets available for MySQL, for example:

##dec8DEC West Europeandec8_swedish_ci1cp850DOS West Europeancp850_general_ci1hp8HP West Europeanhp8_english_ci1koi8r KOI8-R Relcom Russiankoi8r_general_ci1latin1iso-8859-1 West Europeanlatin1_swedish_ci1latin2iso-8859-2 Central Europeanlatin2_general_ci1swe77bit Swedishswe7_swedish_ci1asciiUS ASCIIascii_general_ci1##ujissjishebrewtis620euckrkoi8ugb2312##greekiso-8859- 7 Greekgreek_general_ci1cp1250Windows Central Europeancp1250_general_ci1GBK Simplified Chineseiso-8859-9 Turkish##armscii8ARMSCII-8 Armenian armscii8_general_ci1utf8UTF-8 Unicodeutf8_general_ci 3ucs2_general_cicp866_general_ci##keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1macceMac Central Europeanmacce_general_ci1macromanMac West Europeanmacroman_general_ci1cp852DOS Central Europeancp852_general_ci1141##utf16UTF -16 Unicodeutf16_general_ci4utf16leUTF-16LE Unicodeutf16le_general_ci4cp1256Windows Arabiccp1256_general_ci1cp1257Windows Balticcp1257_general_ci1
Charset Description Default collation Maxlen
big5 Big5 Traditional Chinese big5_chinese_ci 2
EUC-JP Japanese ujis_japanese_ci 3
Shift-JIS Japanese sjis_japanese_ci 2
iso-8859-8 Hebrew hebrew_general_ci 1
TIS620 Thai tis620_thai_ci 1
EUC-KR Korean euckr_korean_ci 2
KOI8-U Ukrainian koi8u_general_ci 1
GB2312 Simplified Chinese gb2312_chinese_ci 2
##gbk
gbk_chinese_ci 2 latin5
latin5_turkish_ci 1
##ucs2 UCS-2 Unicode
2 cp866 DOS Russian
1
##latin7 iso-8859-13 Baltic latin7_general_ci
utf8mb4 UTF-8 Unicode utf8mb4_general_ci
cp1251 Windows Cyrillic cp1251_general_ci
##utf32 UTF-32 Unicode utf32_general_ci 4
binary Binary pseudo charset #binary 1
geostd8 GEOSTD8 Georgian geostd8_general_ci 1
cp932 SJIS for Windows Japanese cp932_japanese_ci 2
eucjpms UJIS for Windows Japanese eucjpms_japanese_ci 3
  1. Set the character set of the database

When we create a new MySQL database, we can set the default character set of the database so that all tables in the database All character sets will use this character set. The specific steps are as follows:

2.1 First check the character sets supported by MySQL

mysql> SHOW CHARACTER SET;

2.2 When creating a new database, add a character set setting Set

CREATE DATABASE new_db CHARACTER SET utf8;

or

The following sentence is rewritten: "Create a new database called 'new_db' with a default character set of utf8."

where utf8 is one of the commonly used character sets.

  1. Set the character set of the MySQL table

In order to ensure that the table can store and display data correctly, we need to set the character encoding for the table. In MySQL, the character set of a table can be set when creating the table. Already created tables can be modified using the ALTER command.

3.1 Set the character set when creating the table

CREATE TABLE new_table (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The storage engine used to set the table is ENGINE=InnoDB, and the default character set used to set the table is CHARSET=utf8.

3.2 Modify the character set of the table

ALTER TABLE old_table CONVERT TO CHARACTER SET utf8;

or

ALTER TABLE old_table MODIFY COLUMN name VARCHAR(50) CHARACTER SET utf8;

Among them, the CONVERT TO command is used to modify the default character set of the table, and the MODIFY COLUMN command is used to modify the character set of a column in the table.

  1. Set the character set for MySQL connection

When connecting to the MySQL server, you can also set the character set for the operation. The character set setting, often called "client character set", refers to the character set used for data transmitted on the client side.

4.1 View the character set of the current connection

mysql> SELECT @@character_set_connection;

4.2 Modify the character set of the connection

SET character_set_connection = utf8;

or

mysql --default-character-set=utf8 -u root -p

Among them, the SET command can modify the default character set of the connection, --default-character The -set command can specify the client character set.

  1. Other character set settings

In some cases, in order to handle some less common data storage and conversion requirements, it may be necessary to enable Additional character set settings for MySQL. In this case, you may need to modify the MySQL configuration file-my.cnf file with administrator privileges. The modification method is as follows:

5.1 Find the my.cnf file

In Linux, the my.cnf file is usually stored in the /etc/my.cnf or /etc/mysql/my.cnf directory .

In Windows, the my.cnf file is usually stored in the installation directory of the MySQL database.

5.2 Modify the my.cnf file

Add the following statement in the my.cnf file:

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'

Among them, the character_set_server command is used to set the listed character set, and the init_connect command is used to automatically set the character set when creating a connection.

The above is the detailed content of How to set character set in mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
Adding Users to MySQL: The Complete TutorialAdding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageThe Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.