MySQL - 插入/更新字符串值，不带引号-解网

问：

我创建了一个例程，该例程遍历表并将结果连接在一个变量中。该变量稍后与 PREPARE 和 EXECUTE 一起使用，以一次性插入一堆行。

BEGIN

    DECLARE fetched_name VARCHAR(32);

    DECLARE loop_finished TINYINT(1) UNSIGNED DEFAULT 0;
    DECLARE loop_cursor CURSOR FOR SELECT name FROM first_example_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_finished = 1;

    SET @insert_entry = NULL;

        OPEN loop_cursor;

            looping: LOOP

                FETCH loop_cursor INTO fetched_name;
    
                    IF
                    loop_finished = 1
                    THEN
    
                        LEAVE looping;
    
                    END IF;
    
                SET @insert_list = CONCAT_WS("), (", @insert_list, fetched_name);

            END LOOP;

        CLOSE loop_cursor;

    SET @insert_list = CONCAT("INSERT INTO second_example_temple (name) VALUES (", @insert_list, ")");

    PREPARE insert_query FROM @insert_list;
    EXECUTE insert_query;

END

到目前为止，这实际上会按预期工作。但是，问题在于准备好的查询包含不带引号的值，如下所示：

INSERT INTO second_example_temple (name) VALUES (Bill), (Peter), (Tom);

MySQL拒绝该查询缺少的引号，它期望查询如下所示：

INSERT INTO second_example_temple (name) VALUES ("Bill"), ("Peter"), ("Tom");

这个问题有什么好的解决方法吗？理想情况下，无需手动设置引号，也无需使用 CONCAT 或 CONCAT_WS，因为在名称旁边设置更多字段时会变得非常复杂。

mysql 游标引号声明

CREATE PROCEDURE createEmailList (
  INOUT emailList varchar(4000)
)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE emailAddress varchar(100) DEFAULT "";

  -- declare cursor for employee email
  DEClARE curEmail 
      CURSOR FOR 
          SELECT email FROM employees;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
SET emailList = '';
  OPEN curEmail;

  getEmail: LOOP
      FETCH curEmail INTO emailAddress;
      SET @a = emailAddress;
      IF finished = 1 THEN 
          LEAVE getEmail;
      END IF;
      -- build email list
      SET emailList = CONCAT(QUOTE(emailAddress),";",emailList);
      SET @b = CONCAT(QUOTE(emailAddress),';',emailList);
  END LOOP getEmail;
  CLOSE curEmail;

END

CALL createEmailList(@emaillist)

SELECT @emaillist

| @emaillist                                      |
| :---------------------------------------------- |
| '[email protected]';'[email protected]';'[email protected]'; |

db<>fiddle 在这里

上一个：Powershell 字符串保持 CR 和 LF

下一个：使用 Python 3.6 的 MySQL 查询（字符串变量在单引号中）

MySQL - 插入/更新字符串值，不带引号

mysql - insert/update string values without wrapping quotes

评论