在MySQL中搜索多列以进行行匹配
为此，请使用UNION。让我们首先创建一个表-
mysql> create table DemoTable645 (Id int,FirstName varchar(100));
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable645 values(100,'Chris'); mysql> insert into DemoTable645 values(101,'Robert'); mysql> insert into DemoTable645 values(101,'Bob'); mysql> insert into DemoTable645 values(102,'Carol'); mysql> insert into DemoTable645 values(100,'John'); mysql> insert into DemoTable645 values(100,'Robert'); mysql> insert into DemoTable645 values(101,'Mike'); mysql> insert into DemoTable645 values(101,'John');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable645;
这将产生以下输出-
+------+-----------+ | Id | FirstName | +------+-----------+ | 100 | Chris | | 101 | Robert | | 101 | Bob | | 102 | Carol | | 100 | John | | 100 | Robert | | 101 | Mike | | 101 | John | +------+-----------+ 8 rows in set (0.00 sec)
以下是搜索多列的查询-
mysql> select Id AS Records from DemoTable645 where Id LIKE '%100%' union select FirstName from DemoTable645 where FirstName LIKE '%John%' order by 1 LIMIT 3;
这将产生以下输出-
+---------+ | Records | +---------+ | 100 | | John | +---------+ 2 rows in set (0.00 sec)