mysql - 如何对表中的所有列执行 REGEXP？

Question

如何对所有列执行 REGEXP？

这是我的 table :

mysql> select * from datatables_demo;
+----+------------+-----------+-------------+--------+------------+--------+
| id | first_name | last_name | position    | office | start_date | salary |
+----+------------+-----------+-------------+--------+------------+--------+
|  1 | Tiger      | Nixon     | Accountant  | Tokyo  | 2016-11-08 | 320800 |
|  2 | Garrett    | Winters   | Accountant2 | Tokyo  | 2016-11-08 | 170750 |
|  3 | Ashton     | Cox       | Accountant3 | Tokyo  | 2016-11-08 |  86000 |
|  4 | Cedric     | Kelly     | Accountant4 | Tokyo  | 2016-11-08 | 433060 |
+----+------------+-----------+-------------+--------+------------+--------+
4 rows in set (0.01 sec)

这就是我选择所有列并在 1 列上执行 REGEXP 的方法 first_name:

mysql> select * from datatables_demo WHERE first_name REGEXP 'T';
+----+------------+-----------+-------------+--------+------------+--------+
| id | first_name | last_name | position    | office | start_date | salary |
+----+------------+-----------+-------------+--------+------------+--------+
|  1 | Tiger      | Nixon     | Accountant  | Tokyo  | 2016-11-08 | 320800 |
|  2 | Garrett    | Winters   | Accountant2 | Tokyo  | 2016-11-08 | 170750 |
|  3 | Ashton     | Cox       | Accountant3 | Tokyo  | 2016-11-08 |  86000 |
+----+------------+-----------+-------------+--------+------------+--------+
3 rows in set (0.00 sec)

如何对所有列执行 REGEXP？这是我的尝试，但不正确。

mysql> select * from datatables_demo WHERE * REGEXP 'T';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* REGEXP 'T'' at line 1
mysql>

无法从docs中解决这个问题

我是否必须为每列写出 REGEXP:
从 datatables_demo 中选择 * WHERE 列 1 REGEXP 'T' 或列 2 REGEXP 'T' 或列 N REGEXP 'T';

Answer 1

您可以按列编写它，也可以在正则表达式之前使用 concat。请查看此内容:

SELECT * FROM datatables_demo where CONCAT(first_name, last_name, position, office) REGEXP 'T';