mysql查询in排序(备忘)

最新推荐文章于 2025-03-10 15:27:44 发布
最新推荐文章于 2025-03-10 15:27:44 发布
阅读量284 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql 大小写
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/shenya2/article/details/78344111
本文介绍了MySQL中BINARY关键字的作用及FIELD函数的使用方法。BINARY用于确保字符比较时考虑大小写，FIELD函数则可以指定ORDER BY的顺序。通过具体SQL示例展示了如何在查询中应用这两个功能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

废话不多说，直接上代码，这里也只做一个备忘。

SELECT * FROM TABLE a WHERE a.ORG_ID='111111' AND BINARY a.NO IN ('Z210',
'Z209',
'z278',
'z333',
'787',
'z288') ORDER BY FIELD(a.NO,'Z210',
'Z209',
'z278',
'z333',
'787',
'z288');


其实用法很简单，就是在 ORDER BY 后面衔接FIELD函数，第一个参数是in里面数据相关的字段。关于性能、或是是否适用复杂查询，就得继续百度啦！！

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

额外记录一个知识点：

mysql在执行过程查询过程中有可能会对数据的大小写不敏感，怎么解决呢？

》》》还是上面的sql 注意 BINARY 关键字。经过测试是正常的！

涉及相关解决方案的博客(感谢大牛的无私奉献)：http://blog.youkuaiyun.com/u013410747/article/details/52931824；

SQLite备忘笔记
finedayforu的博客
06-30 201
一. SQLite简介 SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。 就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。 SQLite特点 不需要一个单独的服务器进程或操作的系统(无服务器的)...
MySQL性能优化之索引设计
IT王小二的博客
03-08 458
作者：IT王小二 博客：https://itwxe.com 上一篇给小伙伴们讲了关于SQL查询性能优化的相关技巧，一个好的查询SQL离不开合理的索引设计。这篇小二就来唠一唠怎么合理的设计一个索引来优化我们的查询速度，要是有不合理的地方…嗯… 当然啦，开个玩笑，欢迎小伙伴们指正！ 一、索引设计基石 索引设计基石是什么呢？ 小二认为索引设计的基石就是数据表字段类型的合理设计，即选择合适字段类型和设置合适的长度。 选择正确的数据类型，那么在字段上建立索引时，一个数据页可以存储更多的索引，一次读取加载到内.
mysql in 排序
07-26
mysql in 排序 mysql in 排序
mysql in 排序问题：可以按in里面的顺序来排序
weixin_33735077的博客
07-07 383
mysql in 排序问题是一个问题，但是在遇到问题的时候就发现问题了，有时候不是外面想要的东西。譬如，有个场景，一个几万条记录的表，主键是 id，我想从表中取 id 为 30，20，80，40 的几条记录。注意，30，20，80，40，是我预期的顺序，我希望 MySQL 按这样的我提供ID顺序返回记录。于是我这样写 SQL：SELECT * FROM my_table WH...
Mysql in 排序
weixin_30376509的博客
04-17 90
SELECT `id` FROM `topic` WHERE `id` IN ('444','512','447','509','504','445','511') ORDER BY FIELD(id,444,512,447,509,504,445,511) SELECT `id` FROM `topic` WHERE `id` IN ('444','512','447','509','504'...
mysql in 内排序
哈罗思密达的专栏
12-17 464
正常情况下MySQLin的排序会按照数字的顺序进行排序   123456 例      select goods_id,goods_name,goods_keywords,order_price,goods_thumb from goods where goods_id IN (183,104,36,156,87,19,15,153) 得出的结果会按照 数字的大小进行排序,但是
mysql中数据统计的技巧备忘录
09-09
在 MySQL 中，数据统计涉及到多种函数和操作，如聚合函数、日期时间函数、分组和排序等。下面我们将通过几个具体的例子来深入探讨 MySQL 中的数据统计技巧。 1. **按天统计进单量** 这个例子中，我们使用 `DATE_...
【检索性能提升】：备忘录管理系统查询优化的关键技巧
[【检索性能提升】：备忘录管理系统查询优化的关键技巧](https://www.highgo.ca/wp-content/uploads/2021/04/HashIndexes.png) # 摘要 本文系统地探讨了备忘录管理系统的相关技术与性能优化方法。首先概述了备忘录...
sql语句练习50题(Mysql版)
IsNinetyBro的博客
03-20 3437
数据来自@廖致君 参考@启明星的指引 数据字典 可以复制到电脑的记事本或者备忘录，方便及时查阅，加粗的是主键。 1、学生表 Student(Sid, Sname, Sage, Ssex)——学生表（学号，姓名，出生日期，性别） 2、课程表 Course(Cid, Cname, Tid)——课程表（课程编号，课程名称，教师编号） 3、教师表 Teacher(Tid, Tname)——教师表（教师编...
MySQL in语句排序
Jack huang的专栏
03-31 329
//方法一 SELECT*FROM tablename WHERE id IN(2,3,1)ORDER BY INSTR(',"2,3,1",',CONCAT(',',id,',')); //方法二 SELECT*FROM tablename WHERE id IN(2,3,1)ORDER BY FIND_IN_SET(id,"2,3,1"); //方法三 SELECT*...
mysql in 排序_MySQL对IN（）中的参数进行排序
weixin_31477659的博客
01-18 165
I have string containing a lot of IDs corresponding to my database.Like:1,2,3,4,5I then do a mySQL query to select all those rows:SELECT * FROM `table` WHERE `id` IN (".$myIDs.")I want mySQL to return...
mysql in操作_MySQL查询in操作排序
weixin_39525243的博客
01-18 113
in操作排序先说解决方案：select * from test where id in(3,1,5) order by field(id,3,1,5);或许有人会注意过，但我以前真不知道SQL: select * from table where id IN (3,6,9,1,2,5,8,7);这样的情况取出来后，其实，id还是按1,2,3,4,5,6,7,8,9,排序的，但如果我们真要按IN里面...
【mysql】根据in的顺序排序
王佑辉的博客
03-10 621
1.在 MySQL 中，IN 子句通常用于过滤特定的记录。然而，IN 子句本身并不能保证结果按特定的顺序返回。4.性能问题：FIELD() 函数在排序时需要额外的计算开销。如果你的数据集很大，这种方法可能会影响查询性能。5.适用性：这种方法适用于小型数据集或需要特定排序的场景。如果你有更复杂的排序需求，可能需要考虑其他方法。2.如果你希望结果按照 IN 子句中指定的顺序返回，可以使用 FIELD() 函数。
Mysql中in语句排序
无业游民
07-22 130
这只是Mysql语句的写法，不同数据库写法不太一样, SELECT * FROM ao_member_tab where id in(1,11115,11140,11135) order by instr('1,11115,11140,11135',id); 如果不使用order by，in语句查询出来的顺序是乱的 ...
mysql in 查询 按in的顺序来排序
天天的专栏
04-08 9092
一个小知识点 如果按in查询时，想让结果记录按照in里的记录顺序排序。 举例：SQL: select * from table where id IN (3,9,6); 查询结果的属性，会按记录id 3、6、9的顺序配置。这时我想让记录按照in里的3、9、6来排序。 mysql为我们提供了这个功能：SQLselect * from table where id IN (3,9,6) or...
mysql in查询排序问题
Lamborrt的博客
06-21 503
项目中有个地方用到了in查询,一般in查询排序跟id列表是无关的,但此处要求按照id列表(从另一张表查询得到,该表无主键)排序,一开始没注意,直接 id in (ids),没想到查出来的数据是按id默认排序的,刚好前不久用过select … id in (select id …)这种sql,而且顺序是按照中间表查到的id来的.不禁十分郁闷,网上查了下,发现mysql提供了解决方案,还有根据id在i...
Mysql使用in查询时，结果按in集合顺序排序
Marclew_的博客
07-04 486
Mysql使用in查询时，结果按in集合顺序排序
mysql的in选择结果过，按照in里面的排序排序
lixld的专栏
01-25 3243
因为如果不用下面的order by field(列名字，数字)，则结果是按照id的自然干顺序排的 只有加了下面的ｏｒｄｅｒ　ｂｙ　后才是按照ｉｎ里面的顺序排序！ select user.us_fansno as '号',sub.usb_score as '积分'  from b_user user,b_usersub sub  where user.us_id = sub.us_id 
from FEE_LIST_D a LEFT JOIN SETL_D b on a.SETL_ID = b.SETL_ID AND a.FIXMEDINS_CODE = b.FIXMEDINS_CODE LEFT JOIN MDTRT_D c on a.MDTRT_ID = c.MDTRT_ID AND a.FIXMEDINS_CODE = c.FIXMEDINS_CODE 字符集不匹配
最新发布
08-01
<think>我们面对的问题是SQL中LEFT JOIN时因字符集不匹配导致的错误（如ORA-12704）。根据引用[1][2][4]，字符集不一致不仅可能引发错误，还会导致索引失效，影响查询性能。以下将系统性地解决该问题： --- ### 一、诊断字符集不匹配 首先确认连接字段的字符集差异（引用[1][2][4]）： ```sql -- Oracle 示例 SELECT a.table_name, a.column_name, a.data_type, a.char_length, b.table_name, b.column_name, b.data_type, b.char_length FROM all_tab_columns a JOIN all_tab_columns b ON a.column_name = b.column_name WHERE a.table_name IN ('FEE_LIST_D', 'SETL_D', 'MDTRT_D') AND b.table_name IN ('FEE_LIST_D', 'SETL_D', 'MDTRT_D') AND a.column_name IN ('SETL_ID', 'FIXMEDINS_CODE', 'MDTRT_ID'); ``` 若发现类似以下差异即为问题根源： | 表名 | 字段名 | 数据类型 | 字符集 | |------------|----------------|------------|------------| | FEE_LIST_D | FIXMEDINS_CODE | VARCHAR2 | AL32UTF8 | | SETL_D | FIXMEDINS_CODE | NVARCHAR2 | AL16UTF16 | --- ### 二、5种解决方案（按优先级排序） #### 方案1：显式转换字符集（立即生效，推荐） 在JOIN条件中使用`CAST`或`TO_CHAR`/`TO_NCHAR`统一字符集（引用[4]）： ```sql SELECT ... FROM FEE_LIST_D a LEFT JOIN SETL_D b ON a.SETL_ID = b.SETL_ID AND TO_NCHAR(a.FIXMEDINS_CODE) = b.FIXMEDINS_CODE -- 统一为NVARCHAR2 LEFT JOIN MDTRT_D c ON TO_NCHAR(a.MDTRT_ID) = c.MDTRT_ID AND TO_NCHAR(a.FIXMEDINS_CODE) = c.FIXMEDINS_CODE; ``` > **注意**：转换可能导致索引失效（引用[1][2]），需在转换字段创建函数索引： > ```sql > CREATE INDEX idx_fixmedins_nchar ON FEE_LIST_D(TO_NCHAR(FIXMEDINS_CODE)); > ``` #### 方案2：修改列字符集（需停机维护） 永久修改字段类型（需ALTER权限）： ```sql ALTER TABLE FEE_LIST_D MODIFY FIXMEDINS_CODE NVARCHAR2(20); ALTER TABLE SETL_D MODIFY FIXMEDINS_CODE NVARCHAR2(20); ``` > **风险**：大表修改可能锁表，需评估业务影响。 #### 方案3：使用数据库会话设置（临时方案） 调整NLS参数强制兼容比较（引用[4]）： ```sql ALTER SESSION SET NLS_COMP = LINGUISTIC; ALTER SESSION SET NLS_SORT = BINARY_CI; ``` > **局限**：仅解决比较错误，不解决存储差异。 #### 方案4：创建视图统一字符集 ```sql CREATE VIEW v_fee_list AS SELECT SETL_ID, TO_NCHAR(FIXMEDINS_CODE) AS FIXMEDINS_CODE, MDTRT_ID FROM FEE_LIST_D; CREATE VIEW v_setl AS SELECT SETL_ID, FIXMEDINS_CODE FROM SETL_D; -- 假设已是NVARCHAR2 -- 查询时直接JOIN视图 SELECT * FROM v_fee_list a LEFT JOIN v_setl b USING (SETL_ID, FIXMEDINS_CODE); ``` #### 方案5：数据清洗转换（ETL过程） 在数据加载阶段统一字符集： ```sql -- 示例：从源表加载时转换 INSERT INTO FEE_LIST_D(SETL_ID, FIXMEDINS_CODE) SELECT SETL_ID, TO_NCHAR(FIXMEDINS_CODE) FROM source_table; ``` --- ### 三、性能优化建议 1. **索引策略** - 为转换后的字段创建函数索引（方案1必需） - 复合索引包含字符集转换字段： ```sql CREATE INDEX idx_setl_combo ON SETL_D(SETL_ID, TO_NCHAR(FIXMEDINS_CODE)); ``` 2. **避免全表扫描** 添加过滤条件缩小数据集： ```sql WHERE a.visit_date > SYSDATE - 30 ``` 3. **监控执行计划** 使用`EXPLAIN PLAN`检查是否使用索引（引用[1][2]）： ```sql EXPLAIN PLAN FOR SELECT ... FROM ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` --- ### 四、字符集选择最佳实践 1. **新系统设计** - 所有关联字段使用`NVARCHAR2`（支持Unicode） - 数据库字符集用`AL32UTF8` 2. **包含Emoji等特殊字符** 必须使用`AL32UTF8`+`NVARCHAR2`（引用[2]） 3. **跨数据库交互** 在应用层统一转码为UTF-8 > **关键结论**：字符集不一致本质是 **存储编码** 与 **比较编码** 的冲突（引用[1][4]），统一编码是根本解决方案。 --- §§相关问题§§ 1. 如何检测 Oracle 数据库中已有的字符集不兼容数据？ 2. 在 AL32UTF8 和 ZHS16GBK 字符集间转换时如何避免数据丢失？ 3. 如何为包含 emoji 的数据选择最佳字符集配置？ 4. Oracle 字符集转换对性能的影响如何评估？ 5. 跨数据库查询时如何确保字符集兼容性？ 6. 如何批量修改已有表的字符集属性？</think>以下是针对 SQL LEFT JOIN 中字符集不匹配问题的系统化解决方案，结合了 Oracle 和 MySQL 的最佳实践： ### 一、问题诊断（先确认根源） ```sql -- Oracle 诊断（检查字段字符集） SELECT column_name, data_type, char_used, char_length FROM all_tab_columns WHERE table_name IN ('FEE_LIST_D', 'SETL_D', 'MDTRT_D') AND column_name IN ('SETL_ID', 'MDTRT_ID', 'FIXMEDINS_CODE'); -- MySQL 诊断（检查字符集和排序规则） SHOW FULL COLUMNS FROM FEE_LIST_D LIKE 'FIXMEDINS_CODE'; SHOW FULL COLUMNS FROM SETL_D LIKE 'FIXMEDINS_CODE'; ``` ### 二、5种解决方案（按优先级排序） #### 方案1：显式转换字符集（立即生效，推荐） ```sql SELECT ... FROM FEE_LIST_D a LEFT JOIN SETL_D b ON a.SETL_ID = b.SETL_ID AND CONVERT(a.FIXMEDINS_CODE USING utf8mb4) = CONVERT(b.FIXMEDINS_CODE USING utf8mb4) -- MySQL /* Oracle: TO_NCHAR(a.FIXMEDINS_CODE) = TO_NCHAR(b.FIXMEDINS_CODE) */ LEFT JOIN MDTRT_D c ON CONVERT(a.MDTRT_ID USING utf8mb4) = CONVERT(c.MDTRT_ID USING utf8mb4); ``` #### 方案2：统一字符集（永久解决） ```sql -- MySQL 修改列字符集 ALTER TABLE FEE_LIST_D MODIFY FIXMEDINS_CODE VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Oracle 修改列类型 ALTER TABLE SETL_D MODIFY FIXMEDINS_CODE NVARCHAR2(20); ``` #### 方案3：创建函数索引（优化性能） ```sql -- Oracle CREATE INDEX idx_fixmedins_convert ON FEE_LIST_D(TO_NCHAR(FIXMEDINS_CODE)); -- MySQL 8.0+ CREATE INDEX idx_fixmedins_convert ON FEE_LIST_D ((CONVERT(FIXMEDINS_CODE USING utf8mb4))); ``` #### 方案4：会话级设置（临时方案） ```sql -- Oracle ALTER SESSION SET NLS_COMP = LINGUISTIC; ALTER SESSION SET NLS_SORT = BINARY_CI; -- MySQL SET NAMES 'utf8mb4'; ``` #### 方案5：通过视图转换 ```sql CREATE VIEW v_fee_list AS SELECT SETL_ID, TO_NCHAR(FIXMEDINS_CODE) AS FIXMEDINS_CODE, ... -- Oracle /* MySQL: CONVERT(FIXMEDINS_CODE USING utf8mb4) */ FROM FEE_LIST_D; -- 后续查询直接JOIN视图 SELECT * FROM v_fee_list a LEFT JOIN SETL_D b ON a.FIXMEDINS_CODE = b.FIXMEDINS_CODE; ``` ### 三、性能优化关键点 1. **索引失效预防**（引用[1][2]） - 字符集转换会导致类型转换索引失效 - 解决方案：为转换后的字段创建函数索引 ```sql -- MySQL 8.0+ 函数索引 CREATE INDEX idx_mdrt_convert ON MDTRT_D ((CONVERT(MDTRT_ID USING utf8mb4))); ``` 2. **连接性能优化** ```sql /* 添加过滤条件减少数据集 */ WHERE a.visit_date > '2023-01-01' ``` 3. **执行计划检查** ```sql -- MySQL EXPLAIN FORMAT=JSON SELECT ...; -- Oracle EXPLAIN PLAN FOR SELECT ...; ``` ### 四、字符集选择最佳实践 | 数据类型 | 推荐字符集 | 适用场景 | |----------------|---------------------|--------------------------| | 中文+英文 | AL32UTF8 (Oracle) | 通用系统 | | | utf8mb4 (MySQL) | | | 含Emoji | utf8mb4 | 移动端应用 | | 纯ASCII | US7ASCII | 历史系统/性能敏感场景 | | 多语言支持 | AL16UTF16 (NVARCHAR)| 国际化系统 | > **根本原则**：关联字段的字符集、排序规则、数据类型必须三者完全一致[^1][^2] ### 五、错误处理备忘录 | 错误代码 | 原因 | 解决方案 | |---------------|-------------------------------|--------------------------| | ORA-12704 | CHAR与NCHAR比较 | 使用`TO_NCHAR()`统一 | | MySQL 1267 | utf8与utf8mb4比较 | `CONVERT(col USING utf8mb4)` | | ORA-29275 | 部分多字节字符转换失败 | 使用`REPLACE()`清理数据 | | MySQL 1366 | 不正确的字符串值 | 修改字符集或转换数据 | > 提示：使用`DUMP(column)`函数(Oracle)或`HEX(column)`(MySQL)检查实际存储的字节值差异 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值