关于MYSQL正则匹配不准确的问题

经过数次测试，可以确定如下问题：
1、同样的正则表达式'^[a-zA-Z][a-zA-Z\d_-]{{5,19}}$'，用python的re函数，能够有效匹配出大小写字母开头的字符串，但MYSQL不能分辨大小写字母
2、把正则表达试换成'^[0-9][a-zA-Z\d_-]{{5,19}}$'，MYSQL能匹配出数字开头的字符串
3、SQL语言前面的r和f，都去掉过，或者只保留其中一个，也没用
真的百思不得其解！

a = '5z-9ChenSiJie'
b = re.subn(r'^[a-zA-Z][a-zA-Z\d_\-]{5,19}$', '', a)
print(b)
sql1 = rf"""select * from wx_visit_records where REGEXP_LIKE(message,'^[a-zA-Z][a-zA-Z\d_\-]{{5,19}}$')"""
res, header = g.execute_sql(sql1, None)
print(res)
df = pd.DataFrame(res, columns=header)
print(df)

mysql里不需要r和f前缀，用这个命令

SELECT * FROM wx_visit_records WHERE REGEXP_LIKE(message, BINARY '^[a-zA-Z][a-zA-Z\d_\-]{5,19}$')
PS：我觉得mysql里有个posix可以区分大小写的，但是没有数据集供我测试，你或许可以尝试这样
SELECT * FROM wx_visit_records WHERE message REGEXP '^[[:alpha:]][[:alnum:]_-]{5,19}$'

Mysql的正则表达式默认是不区分大小写的，你可以在前面加一个Binary来强制区分

message是什么编码格式？

1、MySQL的正则表达式默认是区分大小写的；
2、MySQL的正则表达式在匹配大小写时与字符集有关。MySQL中的正则表达式使用的是UTF-16编码，而UTF-16编码可以区分大小写；
3、可以使用BINARY关键字或者COLLATE子句来强制正则表达式区分大小写。BINARY关键字也需要看你的表使用的字符集是否支持；

img

4、在MySQL中，没有名为REGEXP_LIKE的函数，MySQL的正则表达式语法可能因版本而异。在较新的版本中，MySQL支持更完整的的正则表达式功能；
5、你正则里面的{{5,19}}，两个花括号代表什么？在MySQL正则表达式中，两个花括号 {{}} 没有任何特殊含义。正则里面，{n,m} 表示匹配前面的表达式至少 n 次但不超过 m 次；
6、在 MySQL 中不区分大小写的字符集，当使用不区分大小写的字符集时，排序和比较行为将不会考虑字符的大小写；

utf8mb4
utf8
utf16
utf32
binary
binary(2-字节的 UTF-8)
binary(4-字节的 UTF-16)
binary(8-字节的 UTF-32)

7、以下是一些测试：
表结构以及字符集：

img

测试数据：

img

区分大小写查询：

img

不区分大小写查询：

img


注意表的字符集，是不区分大小写的。

【以下回答由 ChatGPT 生成】

问题解答:

由于MYSQL默认在进行正则匹配时是不区分大小写的，所以无法区分大小写字母。但是你可以通过设置COLLATE来解决这个问题。

COLLATE关键字用于指定排序规则。你可以使用COLLATE关键字指定一个区分大小写的排序规则，从而使MYSQL在正则匹配中能够区分大小写字母。

下面是一个示例查询，使用COLLATE utf8_bin：

SELECT * FROM wx_visit_records
WHERE message REGEXP '^[a-zA-Z][a-zA-Z\d_\-]{5,19}$' COLLATE utf8_bin

在这个例子中，我在正则表达式后面使用了COLLATE utf8_bin。COLLATE utf8_bin表示使用utf8字符集进行排序，并区分大小写。

你可以根据需要选择不同的COLLATE规则。

希望以上解答能够帮到你！如果还有其他问题，请随时提问。


如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

引用chatgpt内容作答：
在MySQL中，正则表达式匹配默认是不区分大小写的。如果你希望MySQL进行大小写敏感的正则匹配，可以在查询中显式指定大小写敏感的规则。你可以使用BINARY关键字来强制MySQL进行大小写敏感的匹配。

修改你的SQL查询语句如下：

sql1 = r"""select * from wx_visit_records where REGEXP_LIKE(message, BINARY '^[a-zA-Z][a-zA-Z\d_\-]{5,19}$')"""

通过添加BINARY关键字，MySQL将会进行大小写敏感的匹配，这样与Python的re函数结果应该会更一致。

此外，你在正则表达式中使用了{{5,19}}表示重复，但在Python的re库中应该是{5,19}，请注意去掉其中的一个花括号。在修改后的SQL查询语句中，也需要修正这一部分。

修正后的完整代码如下：

import re
import pandas as pd

# Your input data
a = '5z-9ChenSiJie'

# Using Python re library
b = re.subn(r'^[a-zA-Z][a-zA-Z\d_\-]{5,19}$', '', a)
print(b)

# SQL query with BINARY keyword for case-sensitive matching
sql1 = r"""select * from wx_visit_records where REGEXP_LIKE(message, BINARY '^[a-zA-Z][a-zA-Z\d_\-]{5,19}$')"""
res, header = g.execute_sql(sql1, None)
print(res)
df = pd.DataFrame(res, columns=header)
print(df)

MySQL的正则表达式语法与Python的re库的语法有一些细微差别，因此在编写正则表达式时需要特别留意。同时，在使用BINARY关键字时，性能可能会有所下降，因为大小写敏感的匹配通常会比不区分大小写的匹配消耗更多的资源。

mysql中的的正则表达式默认是不区分大小写的，你需要手动设置成区分大小写的模式，你可以用REGEXP替代这个REGEXP_LIKE函数，而且你还需要在正则表达式开头加上一个BINARY关键字，表示区分大小写。你改一下再运行一下看行不行。

你上面的第9行的sql按照这个格式来改一下：

SELECT * FROM wx_visit_records WHERE message REGEXP BINARY '^[a-zA-Z][a-zA-Z\d_\-]{5,19}$';

如果以上回答对您有所帮助，点击一下采纳该答案～谢谢

我看出以下几点原因可能导致这个问题:

  1. MySQL的正则表达式引擎和Python中的re模块不同,性能和功能会有差异。

  2. MySQL中的正则匹配默认是区分大小写的,所以大小写字母被区别对待。可以使用BINARY关键字来实现忽略大小写的匹配。

  3. MySQL的正则语法对某些特殊字符如[]需要进行转义,这个需要注意。

  4. 在MySQL中使用正则匹配时,注意确保字段采用的是utf8mb4字符集,以支持全部Unicode字符。

建议您可以尝试以下SQL语句,使用BINARY关键字忽略大小写,并调整正则表达式的转义:

SELECT * FROM wx_visit_records 
WHERE REGEXP_LIKE(message, BINARY '^[a-zA-Z][a-zA-Z\\d_\\-]{5,19}$')

如果问题仍未解决,也可以考虑不使用正则,改用MySQL提供的LIKE操作符来实现类似的匹配。

希望这些分析和建议可以帮助您解决MySQL中的正则表达式匹配问题。如仍有疑问,欢迎再描述,我会继续帮助您排查。


import re
import pandas as pd

# 替换字符串
a = '5z-9ChenSiJie'
b = re.subn(r'^[a-zA-Z][a-zA-Z\d_\-]{5,19}$', '', a)
print(b)

# 使用正则表达式进行MySQL查询
sql1 = f"""SELECT * FROM wx_visit_records WHERE message REGEXP '^[a-zA-Z][a-zA-Z\d_\-]{{5,19}}$'"""
# 假设 g 是数据库连接对象，执行查询并获取结果
res, header = g.execute_sql(sql1, None)
print(res)

# 将查询结果转换为DataFrame
df = pd.DataFrame(res, columns=header)
print(df)

上述代码中使用了正确的MySQL正则表达式函数REGEXP，并且在Python中使用了re模块来进行字符串的正则替换。

该回答引用chatGPT仅供参考,如有帮助,希望采纳。

感谢你提供的详细信息。根据你的描述，我了解到你在使用MySQL的正则表达式时遇到了一些问题。这可能是由于MySQL默认情况下不区分大小写造成的。

在MySQL中，可以通过修改连接选项或配置文件来启用大小写敏感性。以下是两种可能的方法：

1. 在连接URL中指定大小写敏感性：
   ```python
   import mysql.connector

   config = {
       'user': 'your_username',
       'password': 'your_password',
       'host': 'your_host',
       'database': 'your_database',
       'connection_collation': 'utf8mb4_bin'  # 指定大小写敏感性
   }

   conn = mysql.connector.connect(**config)

  1. 修改MySQL的配置文件（my.cnf或my.ini）：

    [mysqld]
lower_case_table_names = 0  # 设置大小写敏感性为0

[client]
connection_collation=utf8mb4_bin  # 指定连接的大小写敏感性

请注意，修改配置文件可能需要重启MySQL服务器才能生效。

另外，关于Python中的正则表达式，re模块默认是区分大小写的。所以在Python代码中执行re.subn函数时，会正确匹配大小写字母开头的字符串。

希望以上信息对解决你遇到的问题有所帮助。如果还有其他疑问，请随时提问。

```

使用BINARY关键字可以强制MySQL对字符串进行大小写敏感的比较

mysql里面使用正则表达式，如果要区分大小写，可以在前面添加BINARY关键字。

区分大小写:
SELECT '1AA' REGEXP BINARY '[a-z]'
或：
SELECT '1AA' REGEXP BINARY '[A-Z]'