按分隔符拆分字符串的 MySQL 函数不适用于波兰语特殊字符 [英] MySQL function to split strings by delimiter doens't work with Polish special characters

问题描述

这是一个成功地从字符串中提取单行直到它是带有一些波兰特殊字符的文本的函数

This is a function which successfully grabs single lines out of strings until it's a text with some Polish special characters

DELIMITER $$
DROP FUNCTION SPLIT_STR $$

CREATE FUNCTION SPLIT_STR(x VARCHAR(1500) CHARSET utf8 COLLATE utf8_unicode_ci, delim VARCHAR(12) CHARSET utf8 COLLATE utf8_unicode_ci, pos INTEGER) 
RETURNS VARCHAR(500) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
  DECLARE output VARCHAR(1500) CHARSET utf8 COLLATE utf8_unicode_ci;
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1) 
                 , delim
                 , '');
  RETURN output;
END $$

如您所见，我正在手动设置字符集和排序规则(整个数据库使用的相同).我也试过没有字符集和排序规则设置，但它不起作用.

As you can see, I am manually setting charset and collation (the same that whole database uses). I have also tried without charset and collation settings and it doesn't work.

要重现的输出(这就是它作为单个字段存储在数据库中的方式):

Output to reproduce (that's how it's stored in DB as a single field):

śńąśąńśąńśąńóńśńąśąńśąńśąńóń
śńąśąńśąńśąńóń
sas

做事

SELECT
SPLIT_STR(slides.content1, '\n', 1), 
SPLIT_STR(slides.content1, '\n', 2), 
SPLIT_STR(slides.content1, '\n', 3), 

我实际上只得到第一行(其他 2 个字段为空)

I actually only get the first line (the other 2 fields are empty)

śńąśąńśąńśąńóńśńąśąńśąńśąńóń

推荐答案

CHAR_LENGTH() 返回以字符为单位的长度，而 LENGTH() 以字节为单位返回长度.当您打算处理以字符为单位的长度时，应始终使用 CHAR_LENGTH()，尤其是在处理多字节字符集时，两个函数之间的结果可能不同.

CHAR_LENGTH() returns the length in characters, while LENGTH() returns the length in bytes. You should always use CHAR_LENGTH() when you intend to deal with the length in characters, and especially when dealing with multi-byte character sets, where the result between the two functions may differ.

在你的函数中用 CHAR_LENGTH() 替换 LENGTH() 可能会解决这个问题.

Replacing LENGTH() with CHAR_LENGTH() in your function will likely fix the issue.

这篇关于按分隔符拆分字符串的 MySQL 函数不适用于波兰语特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！