问题与传递口音到MySQL存储过程

问题描述：

如果我运行在存储过程中此查询，匹配行返回正确问题与传递口音到MySQL存储过程

select brand from article where brand regexp '[àéëË]';

但是，如果我尝试转变成一个动态的声明中像

set @s=concat('select brand from article where brand regexp \'[',argument,']\''); 
prepare stmt from @s; 
execute stmt;

然后当我通过“àéëË”作为参数的程序（未找到匹配行）失败。但是，它的工作原理没有重音（“AEE”）。

[编辑]，它甚至没有使用硬编码值

set @s=concat('select brand from article where brand regexp \'[àéëË]\'');

任何想法工作？感谢

答

检查11.1 Character Set Support在表格中。在Rextester

mysql> DROP PROCEDURE IF EXISTS `sp_test`; 
Query OK, 0 rows affected (0.00 sec) 

mysql> DROP TABLE IF EXISTS `_article`; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> CREATE TABLE IF NOT EXISTS `_article` (
    ->  `brand` VARCHAR(255) 
    ->) DEFAULT CHARACTER SET=latin1 COLLATE=latin1_bin; 
Query OK, 0 rows affected (0.00 sec) 

mysql> INSERT INTO `_article` 
    ->  (`brand`) 
    -> VALUES 
    ->  ('àéëË'), 
    ->  ('aeE'); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2 Duplicates: 0 Warnings: 0 

mysql> DELIMITER // 

mysql> CREATE PROCEDURE `sp_test` (`index` TINYINT, `argument` VARCHAR(255)) 
    -> BEGIN 
    ->  SET @`query` := CONCAT('SELECT ', `index`, ' `index`, `brand` 
    '>      FROM `_article` 
    '>      WHERE `brand` REGEXP \'[', `argument`, ']\''); 
    ->  PREPARE `stmt` FROM @`query`; 
    ->  EXECUTE `stmt`; 
    ->  SET @`query` := NULL; 
    ->  DEALLOCATE PREPARE `stmt`; 
    -> END// 
Query OK, 0 rows affected (0.00 sec) 

mysql> DELIMITER ; 

mysql> CALL `sp_test`(1, 'àéëË'); 
+-------+----------+ 
| index | brand | 
+-------+----------+ 
|  1 | àéëË  | 
+-------+----------+ 
1 row in set (0.00 sec) 

Query OK, 0 rows affected (0.00 sec) 

mysql> CALL `sp_test`(2, 'e'); 
+-------+-------+ 
| index | brand | 
+-------+-------+ 
|  2 | aeE | 
+-------+-------+ 
1 row in set (0.00 sec) 

Query OK, 0 rows affected (0.00 sec) 

mysql> CALL `sp_test`(3, 'á'); 
Empty set (0.00 sec) 

Query OK, 0 rows affected (0.00 sec) 

mysql> CALL `sp_test`(4, 'A'); 
Empty set (0.00 sec) 

Query OK, 0 rows affected (0.00 sec) 

mysql> CALL `sp_test`(5, 'Ë'); 
+-------+----------+ 
| index | brand | 
+-------+----------+ 
|  5 | àéëË  | 
+-------+----------+ 
1 row in set (0.01 sec) 

Query OK, 0 rows affected (0.01 sec)

实施例：

我不能与字符集和整理在表中使用重现该问题。

问题与传递口音到MySQL存储过程

相关推荐