当前位置：网站首页>Get all table names and table names with time strings in Mysql Use BetweenAnd to filter the interval range

Get all table names and table names with time strings in Mysql Use BetweenAnd to filter the interval range

2022-08-07 10:02:00 【Domineering rogue temperament】

Scenario

SpringBoot+Mybatis realizes the code to obtain the table building statement and realize dynamic table building:

SpringBoot+Mybatis implements code to obtain table building statements and realizes dynamic table building_The blog of domineering rogue temperament-CSDN Blog

Based on the dynamic table creation above, a table will be generated in the database every day as follows

Then how to get all the table names in the code, and query the table names within this period according to the time interval.

Note:

Blog:
DomineeringA blog with rogue temperament

Implementation

1. Query the sql of all table names in Mysql

SELECT table_name FROM information_schema.TABLES WHERE table_schema = (select database())

where to get the names of all tables

SELECT table_name FROM information_schema.TABLES

To get the name of the table from a specific database

SELECT table_name FROM information_schema.TABLES WHERE table_schema = ''

Get the current database name

select database()

2. Now what we need is to query the data within the specified range according to the automatically generated table name with time string

For example, we only need two table names, bus_badao_record20220804 and bus_badao_record20220805.

You can use the Between And statement.

If the compared field is a string in time format, the data in the corresponding range can be automatically filtered out.

For verification, create a new test table

The date field is of varchar type, and then to filter out the data in the range from 20220804 to 20220805, you can write like this

SELECT date from badao where date between '20220804' and '20220805'

Not only that, if you add the same prefix to each data, the filtering effect can still be achieved

SELECT date from badao where date between 'badao20220804' and 'badao20220805'

So the table name within the specified range of the filter above can be written like this

SELECT table_name FROM information_schema.TABLES WHERE table_schema = (select database()) AND table_name BETWEEN 'bus_badao_record20220804' AND 'bus_badao_record20220805'

原网站

版权声明
本文为[Domineering rogue temperament]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/219/202208071000400839.html

边栏推荐

猜你喜欢

随机推荐