MySQL 如何检查多列是否有单个值

您可以使用IN运算符检查一个值在多个列中是否出现。语法如下 –

select *from yourTableName where value IN(yourColumnName1, yourColumnName2,......N);

为了理解上述概念，让我们创建一个带有一些列的表。创建表的查询如下 –

mysql> create table OneValueFromAllColumns
   −> (
   −> StudentId int,
   −> StudentFirstname varchar(200),
   −> StudentLastname varchar(200),
   −> StudentAge int
   −> );
Query OK, 0 rows affected (1.41 sec)

使用SELECT语句向表中插入一些记录。查询如下 –

mysql> insert into OneValueFromAllColumns values(1,'John','Smith',23);
Query OK, 1 row affected (0.14 sec)

mysql> insert into OneValueFromAllColumns values(2,'Carol','Taylor',22);
Query OK, 1 row affected (0.18 sec)

mysql> insert into OneValueFromAllColumns values(3,'Maria','Garcia',19);
Query OK, 1 row affected (0.16 sec)

mysql> insert into OneValueFromAllColumns values(4,'Bob','Wilson',21);
Query OK, 1 row affected (0.22 sec)

显示我们以上插入的所有记录。查询显示表中的所有记录如下 –

mysql> select *from OneValueFromAllColumns;

以下是输出 –

+-----------+------------------+-----------------+------------+
| StudentId | StudentFirstname | StudentLastname | StudentAge |
+-----------+------------------+-----------------+------------+
|          1 | John            | Smith           |         23 |
|          2 | Carol           | Taylor          |         22 |
|          3 | Maria           | Garcia          |         19 |
|          4 | Bob             | Wilson          |         21 |
+-----------+------------------+-----------------+------------+
4 rows in set (0.00 sec)

以下是通过检查多个列的单个值查找记录的查询。我们将在多个列中（即StudentId、StudentFirstname、StudentLastname和StudentAge）检查值“Taylor”。

查询如下 –

mysql> select *from OneValueFromAllColumns where 'Taylor' IN(StudentId,StudentFirstname,StudentLastname,StudentAge);

以下是显示值“Taylor”记录的输出 –

+-----------+------------------+-----------------+------------+
| StudentId | StudentFirstname | StudentLastname | StudentAge |
+-----------+------------------+-----------------+------------+
|         2 | Carol            | Taylor          | 22         |
+-----------+------------------+-----------------+------------+
1 row in set, 4 warnings (0.03 sec)

阅读更多：MySQL 教程