mysql特殊查询

查询mysql中表名包含'xxx'，然后varchar类型字段包含'xxx'的所有表

# 执行此操作时，可能会受到 GROUP_CONCAT_MAX_LEN 的限制，导致生成的查询语句过长。可以通过以下命令临时增加该限制
SET SESSION group_concat_max_len = 1000000;
# 数据库名
SET @db_name = 'plmcbiz';
# 要查询的信息
SET @search_value = 'LC0026';

SELECT GROUP_CONCAT(query SEPARATOR ' UNION ALL\n') AS full_query
FROM (
         SELECT CONCAT(
                        'SELECT DISTINCT ''', table_name, ''' AS table_name FROM `', table_name,
                        '` WHERE ', column_checks
                ) AS query
         FROM (
                  SELECT
                      table_name,
                      GROUP_CONCAT(CONCAT('`', column_name, '` = ''', @search_value, '''') SEPARATOR ' OR ') AS column_checks
                  FROM information_schema.columns
                  WHERE table_schema = @db_name
                    AND (table_name LIKE '%project%' OR table_name LIKE '%process%')
                    AND data_type IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
                  GROUP BY table_name
              ) AS filtered
         WHERE column_checks IS NOT NULL
     ) AS final;