mysql CONCAT 不断转义

Question

我正在尝试编写一个过程，但在我的 @qr2 中，当我传递 substring_index() 的参数时，CONCAT 被转义。

知道如何将 '-' 传递到函数中，而不让它转义 CONCAT 函数吗？

我尝试过使用“'-'”或\'-\'，但没有任何效果，它一直在转义。

DELIMITER //
CREATE PROCEDURE filter_bar (IN bar_state VARCHAR(20), done_state VARCHAR(20))
BEGIN
set @qr1 = CONCAT('CREATE TABLE ', done_state, ' like ', bar_state, ';');
PREPARE smt from @qr1;
EXECUTE smt;
DEALLOCATE smt;

set @qr2 = CONCAT('UPDATE ', bar_state, ' SET date = SUBSTRING_INDEX(date,'-',-1);');
PREPARE smt from @qr2;
EXECUTE smt;
DEALLOCATE smt;

set @qr3 = CONCAT('INSERT into', done_state,'select name, business_id, date, sum(count) as count from', bar_state,' group by name, business_id, date;');
PREPARE smt from @qr3;
EXECUTE smt;
END //

Answer 1

您可以使用'':

set @qr2=CONCAT('UPDATE ',bar_state,' SET date=SUBSTRING_INDEX(date,''-'',-1);');

您还需要一个空间:

set @qr3 = CONCAT('INSERT into', done_state,' select name, business_id, date, ...'
                                             -- here

最后:

DEALLOCATE smt;
=> 
DEALLOCATE PREPARE smt;

<强> DBFiddle Demo

<小时/>

编辑:

为了避免 SQL 注入(inject)问题，您应该用引号将每个出现的标识符括起来:

The quote_identifier() Function

Given a string argument, this function produces a quoted identifier suitable for inclusion in SQL statements. This is useful when a value to be used as an identifier is a reserved word or contains backtick (`) characters.

bar_state
=>
sys.quote_identifier(bar_state)

done_state
=>
sys.quote_identifier(done_state)