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;#修改连接默认字符集

推荐阅读更多精彩内容

  • show charcater set; show variables like '%character%'; 1....
    SkTj阅读 777评论 0 2
  • MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 6,815评论 5 116
  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 3,441评论 0 9
  • 什么是数据库? 数据库是存储数据的集合的单独的应用程序。每个数据库具有一个或多个不同的API,用于创建,访问,管理...
    chen_000阅读 3,584评论 0 19
  • “你要敢嫁给他,你就不是我的女儿!” “爸!我求求你了,我跟黄剑是真心相爱……” “砰”的一声,没等张意把话说完,...
    红叶拂水阅读 507评论 83 47