TiDB查询发现区分大小写,这个如何设置不区分?

我们项目最近从mysql8.0.14迁移到TiDB v4.0.8版本,现在发现一个小问题,

模糊查询时,之前mysql是不区分大小写的,但TiDB是区分大小写的,这个需要在哪里设置呢?

image

提供下 tidb mysql 中两个表的表结构。

以下是tidb导出来的建表语句,

DROP TABLE IF EXISTS tbl_kol;
CREATE TABLE tbl_kol (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
nickname varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ‘昵称’,
platform varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT ‘社交平台’,
head_img varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ‘头像’,
topic json NULL COMMENT ‘博主类型’,
fans int(11) NOT NULL DEFAULT 0 COMMENT ‘粉丝数量’,
country varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ‘所在地区’,
home_link varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT ‘主页链接’,
register_time int(10) NULL DEFAULT NULL COMMENT ‘注册时间’,
create_time int(10) NULL DEFAULT NULL COMMENT ‘创建时间’,
update_time int(10) NULL DEFAULT NULL COMMENT ‘更新时间’,
last_json json NULL COMMENT ‘最后更新的属性信息’,
email json NULL COMMENT ‘邮箱’,
confirm_email json NULL COMMENT ‘确认邮箱,手动添加’,
social_links json NULL COMMENT ‘社交平台链接’,
is_establish_cooperation tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘是否建立合作关系(0 - 未建立,1 - 已建立)’,
PRIMARY KEY (id) USING BTREE,
UNIQUE INDEX platform(platform, home_link) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3601664 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = ‘kol信息表’ ROW_FORMAT = Compact;

这是查询数据的插入语句,

INSERT INTO tbl_kol(id, nickname, platform, head_img, topic, fans, country, home_link, register_time, create_time, update_time, last_json, email, confirm_email, social_links, is_establish_cooperation) VALUES (295843, ‘Tech YES City’, ‘youtube’, ‘/youtube/avatar/7359b36d1a241615129230d604c326b706734c87.jpg’, ‘[“3C数码”]’, 476000, ‘Australia’, ‘https://www.youtube.com/channel/UC9Tn-atYOt8qZP-oqui7bhw’, NULL, 1599045374, 1609690531, ‘{"_values": {“avatar”: “https://yt3.ggpht.com/ytc/AAUvwnjl112HuZoI6kly4k3OaI5wzc6iF-1XJzoj3FhIzA=s900-c-k-c0x00ffffff-no-rj”, “channel_id”: “UC9Tn-atYOt8qZP-oqui7bhw”, “country”: “Australia”, “email”: [“bryan@techcity.tv”], “fans”: 476000, “home_link”: “https://www.youtube.com/channel/UC9Tn-atYOt8qZP-oqui7bhw”, “nickname”: “Tech YES City”, “platform”: “youtube”, “signature”: “Gaming PCs with the best bang for buck, should you get a AMD ZEN 3 Ryzen? Maybe an NVIDIA RTX 3080 over a 3070? Or avoid NVIDIA and go BIG NAVI with the RX 6900 XT or 6700? If you’re asking these questions, you’re in the right place. Benchmarks, in depth reviews, comparisons on all the latest tech and even compared against used Intel XEON tech for the best price performance.\n\nFor all business inquiries please contact via using e-mail address below.\n\nFor all sponsorship inquiries please inquire here - bryan@techcity.tv\n\nFor sending me over random gear to unbox in vlogs, or test, or something crazy then the PO is:\n\nBryan B.\nPO BOX 546\nNERANG QLD 4211\nAustralia”, “social_links”: {“facebook”: “https://www.facebook.com/TechYESCity”, “instagram”: “http://www.instagram.com/techyescity”, “twitter”: “https://www.twitter.com/techyescity”, “youtube”: “https://www.youtube.com/channel/UC9Tn-atYOt8qZP-oqui7bhw”}, “total_views”: “106728766”, “user_created”: “Dec 28, 2007”, “verified”: 1, “video_count”: “1362”}}’, ‘[“bryan@techcity.tv”]’, NULL, ‘{“facebook”: “https://www.facebook.com/TechYESCity”, “instagram”: “http://www.instagram.com/techyescity”, “twitter”: “https://www.twitter.com/techyescity”, “youtube”: “https://www.youtube.com/channel/UC9Tn-atYOt8qZP-oqui7bhw”}’, 1);

修改以下参数报错,无法修改成功
SET GLOBAL lower_case_table_names = 1;
SET SESSION lower_case_table_names = 1;

SET GLOBAL lower_case_table_names = 1

1105 - Variable ‘lower_case_table_names’ is a read only variable
时间: 0.001s

有办法解决吗?

https://docs.pingcap.com/zh/tidb/stable/character-set-and-collation#新框架下的排序规则支持

修改VARIABLE_VALUE为True变量之后,

UPDATE mysql.tidb SET VARIABLE_VALUE = ‘True’ WHERE VARIABLE_NAME=‘new_collation_enabled’;

再通过滚动重启整个tidb集群,查询原来的语句报错了,

select * from tbl_kol where nickname like ‘%Tech YES City%’

1105 - other error: [components/tidb_query/src/batch/runner.rs:83]: BatchSelectionExecutor: Evaluate error: [components/tidb_query/src/codec/error.rs:213]: invalid schema: UnsupportedCollation { code: -255 }
时间: 0.046s

这是什么原因呢

https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file#new_collations_enabled_on_first_bootstrap

注意看下注意事项。

java 的具体报错建议追下代码,为了更好地开发,建议做一些处理。

navicat连接tidb数据库查询报的错误,还没到代码层面

new_collations_enabled_on_first_bootstrap

  • 用于开启新的 collation 支持
  • 默认值:false
  • 注意:该配置项只有在初次初始化集群时生效,初始化集群后,无法通过更改该配置项打开或关闭新的 collation 框架;4.0 版本之前的 TiDB 集群升级到 4.0 时,由于集群已经初始化过,该参数无论如何配置,都作为 false 处理。

上面的错误是new_collations_enabled_on_first_bootstrap参数导致的吗?安装集群之后是无法更改这个参数吗?

大佬,改这个参数报错呢?还有其他解决办法吗?

是的,只能重建集群把这个参数加上才行。否则当前需求无法满足。

如果报错建议把参数改回去试下呢,已经初始化的集群已经不能使用这个参数了。

这边测试没有这个问题,Navicat 版本如下:
image

PS:不知道 Navicat 使用什么版本,直连 tidb-server 应该不会有报错吧。

大佬,我们今天打算重新安装部署一套全新的TiDB集群系统,配置文件需要修改哪些呢?能帮忙看下吗

topology-bz-test.yaml (5.4 KB)

你好,
这边可能无法帮助你修改这个配置文件,根据之前获得的信息,topology 文件中去掉已关闭服务器,保证测试目的的集群需求即可(3 tikv?5tikv?)

tikv我们现在总共是3台

我们打算今天重新买一批服务器,安装全新的TiDB集群系统,安装需要使用的初始化topology 配置文件需要修改什么吗?

像第一次 deploy 那样即可?第一次不是你部署的吗,可以看文档测试下

topoloy 可以重写,不同纠结,写好 host port dir 即可
https://docs.pingcap.com/zh/tidb/stable/production-deployment-using-tiup

已经解决了,谢谢,在以下节点增加配置即可
server_configs:
tidb:
new_collations_enabled_on_first_bootstrap: true

nice~

加上如下参数后,升级集群可以生效吗?重建的代价太大了
server_configs:
tidb:
new_collations_enabled_on_first_bootstrap: true