MySQL上插入汉字时报错如下,具体见后面分析。 Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1 MySQL字符集相关参数: character_set_server : 服务器字符集 collation_server : 服务器校对规则 character_set_database : 默认
MySQL上插入汉字时报错如下,具体见后面分析。Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1
MySQL字符集相关参数:
character_set_server : 服务器字符集
collation_server : 服务器校对规则
character_set_database : 默认数据库的字符集
collation_database : 默认数据库的校对规则
character_set_client:服务器使用该变量取得链接中客户端的字符集
character_set_connection:服务器将客户端的query从character_set_client转换到该变量指定的字符集。
character_set_results:服务器发送结果集或返回错误信息到客户端之前应该转换为该变量指定的字符集
有两个语句可以设置连接字符集,如下:
A
SET NAMES 'charset_name' 相当于下面三句:
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x; #这个也设置了collation_connection的默认值x
B
SET CHARACTER SET charset_name 相当于下面三句:
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
character_set_results为NULL时,服务器对返回结果集不做任何转换
mysql> SET character_set_results = NULL;
因为字符集编码引起的问题在pg上报的错是:invalid byte sequence for encoding "UTF8",具体见参考
http://blog.csdn.net/beiigang/article/details/39582583
1
mysql> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
2
mysql> create table tb_tt (id int, ctnr varchar(60));
Query OK, 0 rows affected (0.06 sec)
3
mysql> show create table tb_tt;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| tb_tt | CREATE TABLE `tb_tt` (
`id` int(11) DEFAULT NULL,
`ctnr` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)
4
mysql> insert into tb_tt(id,ctnr) values(1,'新華網');
Query OK, 1 row affected (0.02 sec)
5
mysql> select * from tb_tt;
+------+--------+
| id | ctnr |
+------+--------+
| 1 | 新華網 |
+------+--------+
1 row in set (0.02 sec)
6
mysql> set names 'UTF8';
Query OK, 0 rows affected (0.00 sec)
7
mysql> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
8
mysql> insert into tb_tt(id,ctnr) values(2,'新華網');
ERROR 1366 (HY000): Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1
9
If you change the default character set or collation for a database,
stored routines that use the database defaults must be dropped and
recreated so that they use the new defaults. (In a stored routine,
variables with character data types use the database defaults if the
character set or collation are not specified explicitly. See [HELP
CREATE PROCEDURE].)
参考:
http://dev.mysql.com/doc/refman/5.5/en/globalization.html
http://dev.mysql.com/doc/refman/5.5/en/alter-database.html
-----------------
blog.csdn.net/beiigang

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.

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.

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 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 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 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.

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.

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


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

Dreamweaver CS6
Visual web development tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

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.

Zend Studio 13.0.1
Powerful PHP integrated development environment

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool