MySQL游标默默地删除尾随空格

Question

我正在编写一个存储过程，它从给定列中获取一些数据并对其进行处理，问题是其中一些数据包含尾随空格，并且当由于某种原因将数据读入存储过程中的临时表时这些尾随空格被删除(我需要保留它们)。

我已经将罪魁祸首的范围缩小到了游标的声明..

声明 Curs CURSOR FOR SELECT 元素，LENGTH(element) FROM tmpTable；

如果我打印出光标在每个循环中包含的内容，则元素类似于“1234”，但长度为 8..这就是我想要的，因为原始数据是“1234”。

关于如何让光标读取所有内容而不是默默地修剪尾随空格有什么想法吗？

Answer 1

我无法重现该问题。

请注意 LENGTH() 之间的区别和 CHAR_LENGTH() .

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0.00 sec)

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

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

mysql> CREATE TABLE IF NOT EXISTS `tmpTable` (
    ->   `element_utf8` VARCHAR(50),
    ->   `element_latin1` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_spanish_ci
    -> ) CHARACTER SET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tmpTable`
    ->   (`element_utf8`, `element_latin1`)
    -> VALUES
    ->   (CONCAT('1234', SPACE(5)), CONCAT('1234', SPACE(5))),
    ->   ('áéíóú', 'áéíóú');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `element_utf8`,
    ->   `element_latin1`,
    ->   LENGTH(`element_utf8`),
    ->   CHAR_LENGTH(`element_utf8`),
    ->   LENGTH(`element_latin1`),
    ->   CHAR_LENGTH(`element_latin1`)
    -> FROM
    ->   `tmpTable`\G
*************************** 1. row ***************************
                 element_utf8: 1234     
               element_latin1: 1234     
       LENGTH(`element_utf8`): 9
  CHAR_LENGTH(`element_utf8`): 9
     LENGTH(`element_latin1`): 9
CHAR_LENGTH(`element_latin1`): 9
*************************** 2. row ***************************
                 element_utf8: áéíóú
               element_latin1: áéíóú
       LENGTH(`element_utf8`): 10
  CHAR_LENGTH(`element_utf8`): 5
     LENGTH(`element_latin1`): 5
CHAR_LENGTH(`element_latin1`): 5
2 rows in set (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `demo_sp`()
    -> BEGIN
    ->   DECLARE `_not_found` BOOL DEFAULT FALSE;
    ->   DECLARE `_length_utf8`,
    ->           `_char_length_utf8`,
    ->           `_length_latin1`,
    ->           `_char_length_latin1` INT UNSIGNED;
    -> 
    ->   DECLARE `demo_cursor` CURSOR FOR
    ->     SELECT
    ->       LENGTH(`element_utf8`),
    ->       CHAR_LENGTH(`element_utf8`),
    ->       LENGTH(`element_latin1`),
    ->       CHAR_LENGTH(`element_latin1`)
    ->     FROM
    ->       `tmpTable`;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET `_not_found` := TRUE;
    -> 
    ->   OPEN `demo_cursor`;
    -> 
    ->   `read_loop`: LOOP
    ->     FETCH `demo_cursor` INTO `_length_utf8`,
    ->                              `_char_length_utf8`,
    ->                              `_length_latin1`,
    ->                              `_char_length_latin1`;
    -> 
    ->     IF (`_not_found`) THEN
    ->       CLOSE `demo_cursor`;
    ->       LEAVE `read_loop`;
    ->     END IF;
    -> 
    ->     SELECT `_length_utf8`,
    ->            `_char_length_utf8`,
    ->            `_length_latin1`,
    ->            `_char_length_latin1`;
    ->   END LOOP;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `demo_sp`;
+----------------+---------------------+------------------+-----------------------+
| `_length_utf8` | `_char_length_utf8` | `_length_latin1` | `_char_length_latin1` |
+----------------+---------------------+------------------+-----------------------+
|              9 |                   9 |                9 |                     9 |
+----------------+---------------------+------------------+-----------------------+
1 row in set (0.01 sec)

+----------------+---------------------+------------------+-----------------------+
| `_length_utf8` | `_char_length_utf8` | `_length_latin1` | `_char_length_latin1` |
+----------------+---------------------+------------------+-----------------------+
|             10 |                   5 |                5 |                     5 |
+----------------+---------------------+------------------+-----------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)