search
HomeDatabaseMysql TutorialMySQL乱码处理及字符集相关介绍_MySQL

bitsCN.com

  • 问题引入:

        我们经常会遇到一些向MySQL数据库中插入中文,但是select出来的时候,却发现是乱码的情况。如我们向表a出入这样一段记录:i

insert into a values('你好helloworld你好','helloworld');

可能当你访问它的时候,会发现他的结果变成如下图所示:

image

那怎么样才能解决这种问题呢?通过下文对MySQL中字符集的一些操作,你将会得到答案!

 

  • 查看库、表字符集命令:

        要解决字符集的问题,首先要知道现在的系统、数据库、表、客户端等使用什么样的字符集,以及系统支持什么字符集等,下面介绍一些获取相关信息的语句:

1.查看数据库支持的所有字符集

show character set;或者show char set;

Image(42)

2.查看当前状态,里面当然包括字符集的设置:

status或者/s

Image(43)

其中Db characterset对应的是数据库目录下的db.opt文件内容:

Image(44)

3.查看系统字符集设置,包括所有的字符集设置:

show variables like '%char%';

得出如何所示结果:

Image(40)

其中的含义如下:

Image(41)

关于connection相关的字符集的官方文档:

  • What character set is the statement in when it leaves the client?

The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

 

  • What character set should the server translate a statement to after receiving it?

For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.

 

  • What character set should the server translate to before shipping result sets or error messages back to the client?
The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names and error messages.

     从上文中可以看出character_set_connection、character_set_client、character_set_results三个字符集什么时候用到。从实际上可以看到,当客户端连接服务器的时候,它会将自己想要的字符集名称发给mysql服务器,然后服务器就会使用这个字符集去设置character_set_connection、character_set_client、character_set_results这三个值。如cmd是用gbk,而mysql workbench是用utf8.

CMD:

image

MySql WorkBench:

image

 

4.查看数据表中字符集设置:

show full columns from tablename;

Image(45)

show create table tablename/G;

Image(46)

 

5.查看数据库编码:

show create database dbname;

Image(47)

 

  • 创建时指定字符集:

        知道了怎么查找字符集的相关信息之后,我们就要懂得怎么在创建指定对象的时候,为该对象匹配相应的字符集。

1.服务器级:

在安装MySQL时可以设置服务器的默认编码格式,也可对my.ini做修改,修改[mysqld]里面的character_set_server=utf8,则可设置character_set_server的值。

2.数据库级:

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8;

Image(48)

注意,如果不指定默认的字符集,则系统会根据character_set_database的值进行设置,如:

Image(49)

3.表级:

CREATE TABLE  `db_name`.`tb_name` (id VARCHAR(20) NOT NULL,name VARCHAR(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

从下图可看出,定义表的默认字符集为utf8,即使character_set_database为gbk,但是表的列都未utf8

Image(50)

但要注意,如果没有定义表的默认字符集,则他会按照character_set_database的值来设置,如图所示:

Image(51)

4.列级:

CREATE TABLE  `db_name`.`tb_name` (   id varchar(20) NOT NULL,   name varchar(20) CHARACTER SET utf8 );

从下图可以看到,整个表的默认字符集为gbk,所以没有指定字符集的列都用默认的字符集,而指定了字符集的列name,则使用指定的字符集utf8。

Image(52)

 

  • 修改字符集命令

        如果已经是创建好的对象,那又应该如何处理呢。我们就应该对指定对象就行修改字符集的操作。

1.修改character_set_connection、character_set_client、character_set_results三值:

对于某一个连接来说,可以使用:

SET NAMES 'charset_name' [COLLATE 'collation_name']

image

命令

SET NAMES 'charset_name' [COLLATE 'collation_name']

相当于

SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;

另外、还可以修改配置文件,对[mysql]下增加default-character-set=utf8,配置成你想要的字符集。(个人尝试在my.ini里面配置过,没有成效,不知道是不是被使用的客户端想要的字符集给覆盖掉了呢?)

2.修改character_set_database字段:

ALTER DATABASE db_name     [[DEFAULT] CHARACTER SET charset_name]     [[DEFAULT] COLLATE collation_name]

image

3.修改character_set_server字段:

最简单的方法是直接改my.ini配置文件里面[mysqld]的字段,增加character-set-server=gbk,然后重启mysqld,则可改为你想要的字符集。

4.修改表的字符集:

ALTER TABLE tbl_name     [[DEFAULT] CHARACTER SET charset_name]     [COLLATE collation_name]

5.修改列的字符集:

col_name {CHAR | VARCHAR | TEXT} (col_length)     [CHARACTER SET charset_name]     [COLLATE collation_name]

例如:

ALTER TABLE t1 MODIFY     col1 VARCHAR(5)       CHARACTER SET latin1       COLLATE latin1_swedish_ci;

 

  • 参考资料:

MySQL的Character Set Support:  http://dev.mysql.com/doc/refman/5.6/en/charset.html

mysql常用查看库,表字符集命令:  http://bjlfp.blog.163.com/blog/static/773684612012298455765/

MySQL 插入数据时,中文乱码问题的解决: http://www.cnblogs.com/sunzn/archive/2013/03/14/2960248.html

 

 

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

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 Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.