针对mysql(5.7.17)字符集设置(character_set/collation)和mysql中字符集的设置这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展Cannotresolve
针对mysql(5.7.17)字符集设置(character_set/collation)和mysql中字符集的设置这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展Cannot resolve the collation conflict between "Chinese_PRC_CI_AS"、CentOS 6.1下设置MySQL 5.21 字符集设置(gbk)、Collation 差异导致 KingbaseES 与 Oracle 查询结果不同、COLLATION'utf8mb4_unicode_ci'对字符集'utf8'无效-DB :: connection等相关知识，希望可以帮助到你。
本文目录一览：- mysql(5.7.17)字符集设置(character_set/collation)（mysql中字符集的设置）
- Cannot resolve the collation conflict between "Chinese_PRC_CI_AS"
- CentOS 6.1下设置MySQL 5.21 字符集设置(gbk)
- Collation 差异导致 KingbaseES 与 Oracle 查询结果不同
- COLLATION'utf8mb4_unicode_ci'对字符集'utf8'无效-DB :: connection
mysql(5.7.17)字符集设置(character_set/collation)（mysql中字符集的设置）
0 查看字符集(character_set/collation)
use information_schema;
desc tables; --一定记住tables表，information_schema中的metadata都可以查到
desc tables; --一定记住tables表，information_schema中的metadata都可以查到
-- 查看db的默认，DEFAULT_CHARACTER_SET_NAME/DEFAULT_COLLATION_NAME
select * from schemata where schema_name = ''db_name'';
select * from schemata where schema_name = ''db_name'';
-- 查看table的，只有table_collation字段
select * from tables where table_schema = ''db_name'' and table_name = ''table_name'';
select * from tables where table_schema = ''db_name'' and table_name = ''table_name'';
-- 查看column的，CHARACTER_SET_NAME/COLLATION_NAME都要
select * from columns where table_schema = ''db_name'' and table_name = ''table_name'';1234567891011
select * from columns where table_schema = ''db_name'' and table_name = ''table_name'';1234567891011
--查看mysql支持的character_set，Default collation是默认的"大小写敏感"
select * from information_schema.character_sets;
--就是执行了上面的sql
show charset;1234
select * from information_schema.character_sets;
--就是执行了上面的sql
show charset;1234
/*
查看各character set的collation，以gbk为例
gbk_chinese_ci是"大小写不敏感"，ci是case sensitive的缩写
gbk_bin是"大小写敏感"，以二进制(binary)存储
*/
select * from information_schema.collations;
show collation;1234567
查看各character set的collation，以gbk为例
gbk_chinese_ci是"大小写不敏感"，ci是case sensitive的缩写
gbk_bin是"大小写敏感"，以二进制(binary)存储
*/
select * from information_schema.collations;
show collation;1234567
show variables like ''character_set%''; --查看当前环境的character_set1
show variables like ''collation%''; --查看当前的collation1
1 my.ini配置character_set
-- 注意位置，放在[client]和[mysql]都可以，效果相同，以后面的为准。放在[mysqld]下服务不能启动。
default-character-set = utf8
default-character-set = utf8
-- 上句设置之后，对应的全局变量改变
character_set_client = utf8
character_set_connection = utf8
character_set_results = utf8
character_set_client = utf8
character_set_connection = utf8
character_set_results = utf8
-- 注意位置，放在[mysqld]下
character-set-server = gbk
collation-server = gbk_bin
character-set-server = gbk
collation-server = gbk_bin
-- 上句设置之后，对应的全局变量改变
character_set_database = gbk
character_set_server = gbk --默认的create database db时的character_set，其他的含义待测试，可根据字面单词猜想
collation_database = gbk_bin
collation_server = gbk_bin1234567891011121314151617
character_set_database = gbk
character_set_server = gbk --默认的create database db时的character_set，其他的含义待测试，可根据字面单词猜想
collation_database = gbk_bin
collation_server = gbk_bin1234567891011121314151617
2 数据库的character_set指定/修改
-- 默认db的character_set和@@character_set_server一致，table和db一致，column和table一致
create database db;
create database db;
-- defalut可有可无，注意gbk的default collation(上面有)是gbk_chinese_ci(大小写不敏感)
-- 即使collation_database/server都是gbk_bin，创建的db默认仍是gbk_chinese_ci，这里专门指定
create datadbase db character set gbk collate gbk_bin;
create datadbase db default character set gbk collate gbk_bin; --正确
alter datadbase db character set gbk collate gbk_bin; --正确
alter datadbase db convert to character set gbk collate gbk_bin; --错误，不支持convert to1234567891011
3 table的character_set指定/修改
create table t1(c1 varchar2) character set gbk collate gbk_chinese_ci;
-- 测试
insert into t1 values(''a'');
select * from t1 where c1 = ''A''; --返回''a''，此时''a''=''A''
-- 测试
insert into t1 values(''a'');
select * from t1 where c1 = ''A''; --返回''a''，此时''a''=''A''
--注意以下两种方法的差别，第1句是只是修改默认
alter table t1 character set gbk collate gbk_bin; --提示Records: 0
alter table t1 character set gbk collate gbk_bin; --提示Records: 0
--测试
alter table t1 add c2 varchar(50);
insert into t1 values(''b'',''b'');
select * from t1 where c1 = ''A''; --返回''a''，c1仍然是gbk_chinese_ci
select * from t1 where c2 = ''B''; --返回empty，c2是gbk_bin
alter table t1 add c2 varchar(50);
insert into t1 values(''b'',''b'');
select * from t1 where c1 = ''A''; --返回''a''，c1仍然是gbk_chinese_ci
select * from t1 where c2 = ''B''; --返回empty，c2是gbk_bin
--已有字段c1,c2及再添加的默认都是gbk_bin
alter table t1 convert to character set gbk collate gbk_bin; --提示Records: 112345678910111213141516
alter table t1 convert to character set gbk collate gbk_bin; --提示Records: 112345678910111213141516
4 column的character_set指定/修改
-- 套路相同
alter table add c1 varchar(50) character set gbk collate gbk_bin;
alter table t1 change c1 c1 varchar(50) character set gbk collate gbk_bin;
alter table add c1 varchar(50) character set gbk collate gbk_bin;
alter table t1 change c1 c1 varchar(50) character set gbk collate gbk_bin;
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS"
如何更改sql SERVER 2000的排序规则Alter datebase Alter datebase 数据库 Chinese_PRC_BIN
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS
--不区分大小写
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CS_AS
--区分大小写
使用如下命令，可以获得更多的规则：
SELECT *
FROM ::fn_helpcollations()
更改数据库排序规则后，表中字段的排序规则仍然没变，如果在企业管理器中在设计表的界面去一个字段一个字段的改太累人了，
EXEC sp_configure ''allow updates'',1 RECONfigURE WITH OVERRIDE
update dbo.syscolumns set collationid=65572 where collationid=53284
EXEC sp_configure ''allow updates'',0 RECONfigURE WITH OVERRIDE
go
修改数据库的排序规则的时候,要确保你的数据库没有任何连接.
最好在查询分析器中用下面的方法,注意修改数据库名:
/*
关闭用户打开的进程处理
*/
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[p_killspid]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [dbo].[p_killspid]
GO
create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec(''kill ''+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
--关闭用户连接
exec p_killspid ''数据库名''
go
--修改排序规则
Alter datebase Alter datebase 数据库名 Chinese_PRC_BIN
CentOS 6.1下设置MySQL 5.21 字符集设置(gbk)
1. 解决办法： cp /usr/share/MysqL/my-medium.cnf /etc/my.cnf ;2. 编辑：增加两个编码变量
[client]
default-character-set=gbk
[MysqLd]
character_set_server=gbk
datadir=/var/lib/MysqL
socket = /var/lib/MysqL/MysqL.sock
3. 重启MysqL即可。------------------------------------------------------------------------------------------------------------------------------------
1.由于在开发中C++,java，asp.net,jsp等程序中使用gbk作为数据库操作编码的话,并且创建表时也是用了gbk编码，由于MysqL使用的是utf-8的编码，这样在程序语言里查询数据库时，会导致数据获取不到的问题。
2. 设置MySQ.L的字符集时，网上大多是在配置文件my.cnf中的[client]和[MysqLd]下增加：default-character-set=gbk,但是配置完之后你重启MysqL服务器时，要么MysqL服务器会卡死在哪里启动不了（ubuntu），或者重启MysqL时报错：The server quit without updating PID file .......;查询日志：/var/lib/MysqL/"主机名".err文件得知，问题原因是由于当前版本的MysqL的配置文件上my.cnf中的[MysqLd]下的default-character-set变量不被识别，正确的解决办法是在[MysqLd]下：设置character_set_server=gbk
===========下面的内容转自网络=================
解决Starting MysqL. ERROR! The server quit without updating PID file
2011-6-21 23:25| 发布者: ishagua| 查看: 2362| 评论: 0|原作者: ishagua
摘要: 首先告诉你一条超级有用的定理，Linux 系统出错的时候一定要先查看错误日志： #cat /var/lib/MysqL/'主机名'.err
MysqLd_safe MysqLd from pid file /var/lib/MysqL/...- ...首先告诉你一条超级有用的定理，Linux 系统出错的时候一定要先查看错误日志：
#cat /var/lib/MysqL/'主机名'.err
110621 13:07:44 MysqLd_safe MysqLd from pid file /usr/local/MysqL/data/www.pc-pub.com.pid ended
110621 13:10:28 MysqLd_safe Starting MysqLd daemon with databases from /usr/local/MysqL/data
110621 13:10:28 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHaraCTER SET 'utf8'
110621 13:10:28 [ERROR] Aborting
110621 13:10:28 [Note] /usr/local/MysqL/bin/MysqLd: Shutdown complete
艹：是字符集和字符冲突了，所以编译的时候要把字符和字符集选项都加上，并且要兼容！修改编译参数：
#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/MysqL -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EMbedDED_SERVER=1 -DWITH_SSL=system
艹，还是有错，再抓日志看看：
#cat /usr/local/MysqL/data/www.pc-pub.com.err
110621 13:58:19 [Note] Recovering after a crash using MysqL-bin
110621 13:58:19 [Note] Starting crash recovery...
110621 13:58:19 [Note] Crash recovery finished.
110621 13:58:19 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'MysqL.host' doesn't exist
110621 13:58:19 MysqLd_safe MysqLd from pid file /usr/local/MysqL/data//www.pc-pub.com.pid ended
这是啥意思呢?这是权限问题！（先修改配置文件再初始化数据库即可！）
[root@www MysqL-5.5.13]# /etc/init.d/MysqL start
Starting MysqL.. SUCCESS!
记住：Linux 系统下发现有错误，记得一定要先查看日志，这样才能在学习中不断进步！
Collation 差异导致 KingbaseES 与 Oracle 查询结果不同
问题引入
前端提了个问题，说是KingbaseES 返回的结果与 Oracle 返回的结果不一样。具体问题如下：
oracle 执行结果：oracle 有结果返回。
SQL> create table t3(id varchar(9));
Table created.
SQL>insert into t3 values(''abcd'');
1 row created.
SQL>select * from t3 where id<=''abc~'';
ID
---------
abcd
KingbaseES 执行结果：无结果返回
test=# create table t3(id varchar(9));
CREATE TABLE
test=# insert into t3 values(''abcd'');
INSERT 0 1
test=# select * from t3 where id<=''abc~'';
id
----
(0 rows)
问题分析
查看数据库排序规则
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
security | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
尝试用 collate "c" 验证
test=# select * from t3 where id<=''abc~'' collate "C";
id
------
abcd
(1 row)
问题结论
1、oracle 默认 collation binary (可以看 dba_users.DEFAULT_COLLATION = ''USING_NLS_COMP'' ，也就是参照nls_database_parameters.NLS_SORT 的值)，二进制的排序可能并不适用于语言的规则。
2、KingbaseES 默认使用语言的规则进行排序，如果要使用二进制排序，可以使用 “C” or "POSIX"
最后再看个差异的例子：
oracle 二进制排序：
SQL> select * from t3 order by 1;
ID
---------
MacDonald
MacIntosh
Macdonald
Macintosh
KingbaseES en_US.UTF-8 排序
test=# select * from t3 order by 1;
id
-----------
Macdonald
MacDonald
Macintosh
MacIntosh
test=# select * from t3 order by id collate "C";
id
-----------
MacDonald
MacIntosh
Macdonald
Macintosh
本文由博客一文多发平台 OpenWrite 发布！
COLLATION'utf8mb4_unicode_ci'对字符集'utf8'无效-DB :: connection
如何解决COLLATION''utf8mb4_unicode_ci''对字符集''utf8''无效-DB :: connection
我想在laravel内部连接到另一个数据库以获取一些数据。 但是我收到了此错误消息：
"sqlSTATE[42000]: Syntax error or access violation: 1253 COLLATION ''utf8mb4_unicode_ci'' is not valid for CHaraCTER SET ''utf8'' (sql: \\nSELECT O.order_id,\\n\\t O.order_time,\\n\\t from_unixtime(O.order_time) AS created_at,\\n\\t O.priceForPay,\\n\\t O.totalPrice,\\n\\t O.totaldiscount,\\n\\t CONCAT(M.name,'' '',M.family) AS customer_full_name,\\n\\t M.username,\\n\\t P.pro_title,\\n\\t OP.product_id,\\n\\t P.product_identifier AS namad_code,\\n\\t OP.user_quantity AS count,\\n\\t O.mob AS mobile,\\n\\t CONCAT(AG.name,AG.family) AS branch_title\\nFROM orders_products OP\\n\\t\\t INNER JOIN orders O ON O.order_id = OP.order_id\\n\\t\\t INNER JOIN members M ON M.username = O.user_id\\n\\t\\t LEFT JOIN members AG ON AG.username = O.agent_id\\n\\t\\t INNER JOIN products P ON P.product_id = OP.product_id\\nWHERE O.pay_status > 0 and O.order_id = 418766)"
这是我的查询
$order = \\DB::connection(''zarsam_MysqL'')
->select("
SELECT O.order_id,O.order_time,from_unixtime(O.order_time) AS created_at,O.priceForPay,O.totalPrice,O.totaldiscount,CONCAT(M.name,M.username,P.pro_title,OP.product_id,.... ");
今天关于mysql(5.7.17)字符集设置(character_set/collation)和mysql中字符集的设置的分享就到这里，希望大家有所收获，若想了解更多关于Cannot resolve the collation conflict between "Chinese_PRC_CI_AS"、CentOS 6.1下设置MySQL 5.21 字符集设置(gbk)、Collation 差异导致 KingbaseES 与 Oracle 查询结果不同、COLLATION'utf8mb4_unicode_ci'对字符集'utf8'无效-DB :: connection等相关知识，可以在本站进行查询。
本文标签：
![[转帖]Ubuntu 安装 Wine方法（ubuntu如何安装wine）](https://www.gvkun.com/zb_users/cache/thumbs/4c83df0e2303284d68480d1b1378581d-180-120-1.jpg)
