mysql打印特定表范围内的数据

Question

我的 mySQL 数据库名称为“conntrack”，带有下表

root@Radius:/temp# mysql -uroot -pMYSQLPASS "use conntrack; show tables;"
+------------+
| 2016-11-24 |
| 2016-11-25 |
| 2016-11-26 |
| 2016-11-27 |
| 2016-11-28 |
| 2016-11-29 |
| 2016-11-30 |
+------------+

表具有以下结构，(示例)

mysql> describe `2016-12-25`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| time     | time        | NO   | MUL | NULL    |       |
| username | varchar(32) | NO   | MUL | NULL    |       |
| srcip    | varchar(15) | NO   | MUL | NULL    |       |
| srcport  | varchar(5)  | NO   | MUL | NULL    |       |
| dstip    | varchar(15) | NO   | MUL | NULL    |       |
| dstport  | varchar(5)  | NO   | MUL | NULL    |       |
| protocol | char(1)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

我想从2016年11月24日到2016年11月26日的表范围中获取dstport字段。如何使用单线命令做到这一点？

Answer 1

试试这个:

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME BETWEEN '2016-11-24' AND '2016-11-26'

已编辑:

SET @S = NULL;
SELECT GROUP_CONCAT(CONCAT('SELECT DSTPORT FROM `', TABLE_NAME, '`') SEPARATOR ' UNION ') INTO @S
FROM (
    SELECT DISTINCT TABLE_NAME, 1 AS GRP
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME BETWEEN '2016-11-24' AND '2016-11-26'
) T
GROUP BY GRP;
PREPARE STMT FROM @S;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

不确定这是否是您想要的，但请尝试一下。