regexp 变量问题

mysql> select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME regexp @m3;        
+-------------+
| TABLE_NAME  |
+-------------+
| user_201112 |
+-------------+
1 row in set (0.00 sec)

mysql> select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME regexp @m2;
+-------------+
| TABLE_NAME  |
+-------------+
| user_201201 |
+-------------+
1 row in set (0.00 sec)

mysql>

这样都可以。

mysql> select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME regexp @m3|@m2;
Empty set (0.00 sec)

mysql>

这样就不行了。

换成字符，又可以了。

mysql> select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME regexp '201201|201112';
+-------------+
| TABLE_NAME  |
+-------------+
| user_201112 |
| user_201201 |
+-------------+
2 rows in set (0.00 sec)

 

已解决。

mysql> select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME regexp BINARY concat(@m3,'|',@m2,'|',@m1);
+-------------+
| TABLE_NAME  |
+-------------+
| user_201112 |
| user_201201 |
| user_201202 |
+-------------+
3 rows in set (0.00 sec)