mysql - 使用 `LIKE` 进行查询的操作数计数错误

Question

我有这个 MySQL 查询:

 delimiter |
 CREATE FUNCTION KPI17 (s VARCHAR(20000)) RETURNS VARCHAR(20000)
 DETERMINISTIC
 BEGIN
    DECLARE str  VARCHAR(2000);
    DECLARE toFind  VARCHAR(2000);
    DECLARE co VARCHAR(2000);
    DECLARE co1 VARCHAR(2000);
    DECLARE co2 VARCHAR(2000);
    DECLARE co3 VARCHAR(2000);
    DECLARE co4 VARCHAR(2000);
    DECLARE co5 VARCHAR(2000);
    DECLARE co6 VARCHAR(2000);
    DECLARE co7 VARCHAR(2000);
    DECLARE co8 VARCHAR(2000);
    DECLARE co9 VARCHAR(2000);
    DECLARE co10 VARCHAR(2000);
    DECLARE co11 VARCHAR(2000);
    DECLARE co12 VARCHAR(2000);
    DECLARE co13 VARCHAR(2000);
    DECLARE co14 VARCHAR(2000);
    DECLARE cpt VARCHAR(20000);
    SET co='';
    SET co1='';
    SET co2='';
    SET co3='';
    SET co4='';
    SET co5='';
    SET co6='';
    SET co7='';
    SET co8='';
    SET co9='';
    SET co10='';
    SET co11='';
    SET co12='';
    SET co13='';
    SET co14='';
    SET cpt='';
    SET str='';
    SET toFind=s;
    SET str =SUBSTRING_INDEX(toFind,'sur',1);
    IF (str like ('%CDR%')) =1 THEN set co1 = 'CDR';
    ElSEIF (str like ('%DL DROP TBF%','%TBF FAIL%','%TBF DROP%','%TBF SR%','%DL TBF SR%','%UL FAIL TBF%','%DATA FAIL%','%FAIL TBF%','%DL DROP DATA%'))=1 THEN set co2 = 'FAIL ET DROP TBF';
    ElSEIF (str like ('%SD DROP%','%SDR%'))=1 THEN set co3 ='SDR';
    ElSEIF (str like ('%FAIL TCH%'))=1 THEN set co4 = 'FAIL TCH';
    ElSEIF (str like ('%HO LEVEL EN DL%','%HO QUALITE%','%HO INTERFERENCE%','%INC HO%'))=1 THEN set co5 = 'DEGRADATION HO';
    ElSEIF (str like ('%DEGRADATION DE TOUS LES KPI CS/PS%','%SLEEPING PS%','%SLEEPING CS%','%BAISSE DE TRAFFIC%'))=1 THEN set co6 = 'DEGRADATION DES KPI CS/PS 2G';
    ElSEIF (str like ('%CSSR%'))=1 THEN set co7 = 'CSSR';
    ElSEIF (str like ('%COUPURE BSS%','%CALL DROP BSS%','%DROP BSS%'))=1 THEN set co8 = 'CALL DROP BSS';
    ElSEIF (str like ('%HSPA DROP%','%HSPA RB DROP%','%HSPA Rb DROP%','%HSPA RAB SETUP%','%HSPA RAB SETUP SR%','%SLEEPING HSPA%','%HSPA SSR%','%HSPA SR%'))=1 THEN set co9 = 'HSPA';
    ElSEIF (str like ('%HSUPA RAB SETUP%','%SLEEPING HSUPA%'))=1 THEN set co10 = 'HSUPA';
    ElSEIF (str like ('%R99 DROP%','%R99 RB DROP%'))=1 THEN set co11 = 'R99';
    ElSEIF (str like ('%RRC SETUP SUCCESS OTHER RATE%','%RRC OTHER%','%RRC OTHERS%','%DEGRADATION RRC CS/PS SETUP%','%RRC SETUP SR%','%RRC SETUP SUCC SERV RATE%','%PS RRC SSR%','%PS RRC SETUP SR%'))=1 THEN set co12 = 'RRC';
    ElSEIF (str like ('%CS RAB%','%CS RAB SETUP%','%CS RAB SETUP SR%','%PS RAB DROP%','%PS RAB SETUP SR%'))=1 THEN set co13 = 'RAB';
    ElSEIF (str like ('%PS DROP%','%CS SERVICE DROP%'))=1 THEN set co14 = 'DEGRADATION DES KPI CS/PS 3G';
    ELSE SET co='vid';END IF;
    set cpt=CONCAT(co,co1,co2,co3,co4,co5,co6,co7,co8,co9,co10,co11,co12,co13,co14);      
return cpt;
END |
delimiter;

我使用了这个查询，其中“title”是所使用的列的名称，“test3”是我将在其中存储结果的列的名称:

    update incident set test3= KPI17 (title) 

但我收到以下错误:

Error code 1241, SQL state 21000: Operand should contain 1 column(s)

我该如何解决这个问题？

Answer 1

正如错误所示，问题在于您不能对 LIKE 操作数使用多个值。

所以，不要有这样的查询:

SELECT *
FROM myTable 
WHERE str LIKE ('%this%', '%that%');

您需要使用OR:

SELECT *
FROM myTable 
WHERE str LIKE '%this%' OR str LIKE '%that%';

另一种选择，来自this答案是使用正则表达式:

SELECT *
FROM myTable
WHERE str REGEXP 'this|that';

这是一个SQL Fiddle示例。