mysql - 将字符串中的单词与单元格进行比较

Question

鉴于以下情况:

create table original (
dbString VARCHAR(255),
c_Alfa TINYINT(4) Default 0,
c_Bravo TINYINT(4) default 0,
c_Charlie  TINYINT(4) default 0
);

INSERT INTO original (dbString) VALUES
('Charlie Echo Delta',0,0,0),
('Foxtrot Golf Hotel',0,0,0),
('Alfa Oscar Tango',0,0,0),
('Charlie Bravo India',0,0,0);

SET @c_string := 'Alfa Bravo Charlie';

在mysql中是否可以将@c_string中的单词(单词是:Alfa，Bravo和Charlie)与表original中的列dbString进行比较并检查如果单元格中出现一个或多个单词。

结果应该是

+---------------------+------------+------------+------------+
| dbString            | c_Alfa     | c_Bravo    | c_Charlie  |
+---------------------+------------+------------+------------+
| Charlie Echo Delta  | 0          | 0          | 1          |
| Foxtrot Golf Hotel  | 0          | 0          | 0          |
| Alfa Oscar Tango    | 1          | 0          | 0          |
| Charlie Bravo India | 0          | 1          | 1          |
+---------------------+------------+------------+------------+

我还创建了一个 fiddle :http://sqlfiddle.com/#!2/8eb2b/1

Answer 1

试试这个

SELECT dbString,
       IF(locate('Alfa',dbString), 1, 0) AS c_Alfa,
       IF(locate('Bravo',dbString), 1, 0) AS c_Bravo,
       IF(locate('Charlie',dbString), 1, 0) AS c_Charlie
FROM original;

检查 SQL Fiddle