mysql - MySQL中的存储过程用空格替换零值

Question

这是我在数据库 MySql 版本 8.0.17 上的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `SP`(IN tun CHAR(100), 
tmonth int(2), 
tparag LONGTEXT, 
tid int(11))
BEGIN

declare 2tun char(100);
declare 2tmonth int(2);
declare 2tyear int(4);
DECLARE 2tparag LONGTEXT;
DECLARE 2tid INT(11);

SET 2tun = tun;
SET 2tmonth = tmonth;
SET 2tyear = YEAR(CURDATE());
SET 2tparag = tparag;
SET 2tid = tid;

SET @s = CONCAT('UPDATE t_contents_', 2tun, '_', 2tmonth, '_', 2tyear, ' 
                 SET contents = \'',2tparag,'\'
                 WHERE sID = \'',2tid,'\';');
                                 
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;                             

END

此代码工作正常。

但是，如果尝试使用其他版本的 SP，我将变量值('\r' OR '\n' OR '\r\n')上的新行替换为空格2tparag

CREATE DEFINER=`root`@`%` PROCEDURE `SP`(IN tun CHAR(100), 
tmonth int(2), 
tparag LONGTEXT, 
tid int(11))
BEGIN

declare 2tun char(100);
declare 2tmonth int(2);
declare 2tyear int(4);
DECLARE 2tparag LONGTEXT;
DECLARE 2tid INT(11);

SET 2tun = tun;
SET 2tmonth = tmonth;
SET 2tyear = YEAR(CURDATE());
SET 2tparag = tparag;
SET 2tid = tid;

SET @s = CONCAT('UPDATE t_contents_', 2tun, '_', 2tmonth, '_', 2tyear, ' 
                 SET contents = \'',REPLACE(2tparag,'\r' OR '\n' OR '\r\n', ' '),'\' 
                 WHERE sID = \'',2tid,'\';');
                                 
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
                                 
END

当我在数据库上写2021变量2tparag时，它用2 21值更新的字段......

如何解决这个问题？

例子

起始字符串是

Neque porro quisquam est qui dolorem ipsum

通过添加当前年份更新此字符串

Neque porro quisquam est qui dolorem ipsum 2021

在数据库上存储

Neque porro quisquam est qui dolorem ipsum 2 21

标签： mysqlstored-procedures

Answer 1

您的查询没有问题

你REPALCE不会像你想要的那样工作，Mysql 只会替换一次出现的 charateers ，所以只有\npmne\r或\n\r

要重新安排您必须使用的所有事件

REPLACE(REPLACE(REPLACE('test\na\rb','\r' , ' '),'\n' , ' '),'\r\n', ' ')

CREATE TABLE t_contents_123_2_2021(sID int, contents LONGTEXT)

INSERT INTO t_contents_123_2_2021 VALUEs(1,'test')

CREATE PROCEDURE `SP`(IN tun CHAR(100), 
tmonth int(2), 
tparag LONGTEXT, 
tid int(11))
BEGIN

declare 2tun char(100);
declare 2tmonth int(2);
declare 2tyear int(4);
DECLARE 2tparag LONGTEXT;
DECLARE 2tid INT(11);

SET 2tun = tun;
SET 2tmonth = tmonth;
SET 2tyear = YEAR(CURDATE());
SET 2tparag = tparag;
SET 2tid = tid;

SET @s = CONCAT('UPDATE t_contents_', 2tun, '_', 2tmonth, '_', 2tyear, ' 
                 SET contents = \'',REPLACE(2tparag,'\r' OR '\n' OR '\r\n', ' '),'\' 
                 WHERE sID = \'',2tid,'\';');
                                 
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
END

✓

CALL SP ('123','02', 'Neque porro quisquam est qui dolorem ipsum',1)

SELECT * FROM t_contents_123_2_2021

标识 | 内容                                  
--: | :--------------------------------------------------------
  1 | Neque porro quisquam est qui dolorem ipsum

db<>在这里摆弄