replace 后乱码

anafin · 2021 年11 月 12 日 15:19

sql 写法,dinfo.approval字段经过replace后乱码,表字符串定义ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin:
select
replace(replace(replace(replace(replace(replace(dinfo.approval, char(8), ‘’), char(9), ‘’), char(10), ‘’), char(11), ‘’), char(12), ‘’), char(13), ‘’) approval_v
,dinfo.approval
结果截图
1636730324(1)

Meditator · 2021 年11 月 13 日 02:28

先要搞清楚char函数作用以及参数意义

mysql> select char(97);
+----------+
| char(97) |
+----------+
| a        |
+----------+
1 row in set (0.00 sec)

mysql> select char(65);
+----------+
| char(65) |
+----------+
| A        |
+----------+
1 row in set (0.00 sec)

官方解释：
char(N)将每一个参数 N 都解释为整数，返回由这些整数在 ASCII 码中所对应字符所组成的字符串。忽略 NULL 值。示例如下：

mysql> SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76')                                 |
+---------------------------------------------------------+
| MySQL                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

而char(8)表示：

Meditator · 2021 年11 月 13 日 02:33

mysql> select replace("国药准字H20043216",char(8),"");
+---------------------------------------------+
| replace("国药准字H20043216",char(8),"")     |
+---------------------------------------------+
| 国药准字H20043216                           |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select replace(replace("国药准字H20043216",char(8),""),char(9),"");
+-----------------------------------------------------------------+
| replace(replace("国药准字H20043216",char(8),""),char(9),"")     |
+-----------------------------------------------------------------+
| 国药准字H20043216                                               |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),"");
+--------------------------------------------------------------------------------------+
| replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),"")     |
+--------------------------------------------------------------------------------------+
| 国药准字H20043216                                                                    |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),"");
+-----------------------------------------------------------------------------------------------------------+
| replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),"")     |
+-----------------------------------------------------------------------------------------------------------+
| 国药准字H20043216                                                                                         |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select replace(replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),""),char(12),"");
+--------------------------------------------------------------------------------------------------------------------------------+
| replace(replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),""),char(12),"")     |
+--------------------------------------------------------------------------------------------------------------------------------+
| 国药准字H20043216                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select replace(replace(replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"");
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| replace(replace(replace(replace(replace(replace("国药准字H20043216",char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"")     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 国药准字H20043216                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Meditator · 2021 年11 月 13 日 02:34

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper

Connection id:          11
Current database:       test
Current user:           tt@127.0.0.1
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.25-TiDB-v5.2.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               4000
Uptime:                 0 sec

Threads: 0  Questions: 0  Slow queries: 0  Opens: 0  Flush tables: 0  Open tables: 0  Queries per second avg: 0.000

mysql> show create table mytest \G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `approval` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> insert into mytest values("国药准字H20043216");
Query OK, 1 row affected (0.13 sec)

mysql> select replace(replace(replace(replace(replace(replace(approval,char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"") as approval_v, approval from mytest;
+-----------------------+-----------------------+
| approval_v            | approval              |
+-----------------------+-----------------------+
| 国药准字H20043216     | 国药准字H20043216     |
+-----------------------+-----------------------+
1 row in set (0.24 sec)






mysql> select replace(replace(replace(replace(replace(replace(approval,char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"") as approval_v, approval,length(replace(replace(replace(replace(replace(replace(approval,char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"")) as approval_v_length,length(approval) as approval_length,TO_BASE64(replace(replace(replace(replace(replace(replace(approval,char(8),""),char(9),""),char(10),""),char(11),""),char(12),""),char(13),"")) as approval_v_hex,TO_BASE64(approval) as approval_hex from mytest;
+-----------------------+-----------------------+-------------------+-----------------+------------------------------+------------------------------+
| approval_v            | approval              | approval_v_length | approval_length | approval_v_hex               | approval_hex                 |
+-----------------------+-----------------------+-------------------+-----------------+------------------------------+------------------------------+
| 国药准字H20043216     | 国药准字H20043216     |                21 |              21 | 5Zu96I2v5YeG5a2XSDIwMDQzMjE2 | 5Zu96I2v5YeG5a2XSDIwMDQzMjE2 |
+-----------------------+-----------------------+-------------------+-----------------+------------------------------+------------------------------+


mysql> show global variables like "%char%";
+--------------------------------------+--------------------------------------------------------+
| Variable_name                        | Value                                                  |
+--------------------------------------+--------------------------------------------------------+
| character_set_client                 | utf8mb4                                                |
| character_set_connection             | utf8mb4                                                |
| character_set_database               | utf8mb4                                                |
| character_set_filesystem             | binary                                                 |
| character_set_results                | utf8mb4                                                |
| character_set_server                 | utf8mb4                                                |
| character_set_system                 | utf8                                                   |
| character_sets_dir                   | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |
| validate_password_special_char_count | 1                                                      |
+--------------------------------------+--------------------------------------------------------+
9 rows in set (0.01 sec)

mysql> show global variables like "%coll%";
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| collation_connection                  | utf8mb4_bin |
| collation_database                    | utf8mb4_bin |
| collation_server                      | utf8mb4_bin |
| tidb_hash_exchange_with_new_collation | ON          |
+---------------------------------------+-------------+
4 rows in set (0.01 sec)

可以看出replace函数没有做任何替换动作

Meditator · 2021 年11 月 13 日 02:43

show global variables like "%char%";
show global variables like "%coll%";
show create table xxx \G
status

输出看看

anafin · 2021 年11 月 15 日 09:28

感谢回复：我这边navicat及dbeaver查询结果乱码，与你那的不一样；这可能是哪里的问题？

show global variables like “%char%”;
输出：
Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8

show global variables like “%coll%”;
输出：
Variable_name Value
collation_connection utf8mb4_bin
collation_database utf8mb4_bin
collation_server utf8mb4_bin

show create table dinfo
输出：
CREATE TABLE dinfo (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键UID/更改了’,
approval varchar(50) DEFAULT NULL COMMENT ‘批准文号，国药准字H20103180’,
PRIMARY KEY (id) ,
KEY approval_idx (approval,spec_cls_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Meditator · 2021 年11 月 15 日 09:54

1）直接mysql client shell连上去试试
2） navicat 端的字符集是怎么设置的？

Joyz · 2021 年11 月 25 日 06:14

您好，请问您的问题解决了吗？

anafin · 2021 年12 月 3 日 15:13

没找到解决办法；

anafin · 2021 年12 月 3 日 15:14

navicat没找到字符集设置

Meditator · 2021 年12 月 4 日 03:09

打开Navicat客户端，右键连接-编辑连接（有的版本似乎叫连接属性）-高级，在编码下拉项里选择了第一项

参考：
https://www.cnblogs.com/windyet/articles/7307513.html

system · 2022 年10 月 31 日 19:25

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。

replace 后乱码

show global variables like “%char%”; 输出： Variable_name Value character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8mb4 character_set_filesystem binary character_set_results utf8mb4 character_set_server utf8mb4 character_set_system utf8

show global variables like “%coll%”; 输出： Variable_name Value collation_connection utf8mb4_bin collation_database utf8mb4_bin collation_server utf8mb4_bin

show global variables like “%char%”;
输出：
Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8

show global variables like “%coll%”;
输出：
Variable_name Value
collation_connection utf8mb4_bin
collation_database utf8mb4_bin
collation_server utf8mb4_bin