我一个库是utf8,以前一个表是latin的,直接从别的地方下复制到/var数据目录下。python和php用这个表都没有问题。但是现在不能在mysql命令行里select这个表。报错:
>>select name from artist where name like "白%";
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
而命令行下直接"select * from artist"不会报错,但是命令行下name字段乱码,breif字段不乱码。
同一个表中为什么会有两个字段不同表现?我该怎么修改这个表才能在命令行下正常查看管理?
show create database db;
+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
show create table artist;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| artist | CREATE TABLE `artist` (
`artistid` smallint(4) NOT NULL AUTO_INCREMENT,
`name` char(16) NOT NULL,
`brief` blob NOT NULL,
PRIMARY KEY (`artistid`)
) ENGINE=MyISAM AUTO_INCREMENT=414 DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
show variables like "%char%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
show variables like "%coll%";
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
【已解决】MySQL的中文字符集问题。
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
【已解决】MySQL的中文字符集问题。
上次由 sanz 在 2012-12-04 13:04,总共编辑 1 次。
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
Re: MySQL的中文字符集问题。
up。。。
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
- yaoms
- 帖子: 4952
- 注册时间: 2007-10-19 14:51
- 来自: 深圳
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
Re: MySQL的中文字符集问题。
终于发现问题,原来是字段类型不一样,BLOB是binary存储,而TEXT是char。把TEXT都改成BLOB,终端下就OK了。一直以为是我的字符集的问题。
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS