mysql error[HY000][1366]解决办法

关于字符编码错误进入mysql终端

mysql> show create database dbname; #查看创建库时字符集
mysql> alter database dbname character set utf-8; #修改存在数据库的字符集

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| study    | CREATE DATABASE `study` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

mysql> show create table tablename; #查看创建表时字符集
mysql> alter table tablename default character set utf8; #修改存在表的字符集

| customers | CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=latin1

mysql> SHOW FULL COLUMNS FROM tablename;#检查数据表所有字段的状态
mysql> alter table tablename change columnname columnname varchar(20) character set utf8 collate utf8_unicode_ci not null default '';#修改表中columnname的字符集

| Field        | Type      | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------+-----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| cust_id      | int(11)   | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| cust_name    | char(50)  | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| cust_address | char(50)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_city    | char(50)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_state   | char(5)   | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_zip     | char(10)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_country | char(50)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_contact | char(50)  | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| cust_email   | char(255) | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |

mysql> show variables like 'char%'; #查看数据库使用的字符集

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin                      |
| character_set_connection | latin                      |
| character_set_database   | latin                      |
| character_set_filesystem | binary                     |
| character_set_results    | latin                      |
| character_set_server     | latin                      |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> set character_set_system=utf8;#修改系统默认字符集
mysql> set character_set_results=utf8;#修改结果默认字符集
mysql> set character_set_server=utf8;#修改服务器默认字符集
mysql> set character_set_database=utf8;#修改数据库字符集
mysql> set character_set_client=utf8;#修改数据库客户端字符集
mysql> set character_set_connection=utf8;#修改连接默认字符集

mysql error[HY000][1366]解决办法

推荐阅读更多精彩内容