In Mysql, the 'datbase' is referred to as 'table schema', and there is no metadata (e.g. create time) for table schema.
Database create time. A work around is to query the minimum table create time and use it as the database create time.
SELECT table_schema AS Database_Name
,MIN(create_time) AS Creation_Time
FROM information_schema.tables
Group by table_schema
The information_schema.tables has metadata info about the tables and table schema.
If Exists Else. The if else logic seems only applicable in a stored procedure. Running it from a query wouldn't work.
if exists (SELECT 1 )
then
select 'exists';
else
select 'not exists';
end if;
And seems 'if not exists ' doesn't work ... The 'not' is not supported.
Delimeter. In mysql, the default delimiter is semi colon ;. When creating a stored procedure, there are multiple statements with ; within the procedure. The creating script needs to know those semi colons are body of the procedure instead of individual statements to run. So it can temporary change the delimiter to something else, e.g. // so the semi colon won't be interpreted as delimiter. At the end, it changes the delimiter back to ;.
Below is an example of creating a stored procedure to update view definition accordingly.
DELIMITER //
drop procedure if exists sys.sp_abc//
create procedure sys.sp_abc()
begin
select database_name, table_name into @dbname, @tablename
from
(
SELECT table_schema AS Database_Name
,table_name
,MIN(create_time) AS Creation_Time
FROM information_schema.tables
where table_schema like 'abc%'
Group by table_schema, table_name
order by creation_time desc
limit 1
)sub;
if exists (SELECT view_definition
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'sys'
AND TABLE_NAME = 'abc_view'
and view_definition like ('%' + @dbname + '%')
and view_definition like ('%' + @tablename + '%')
)
then
select 'the view is up to date';
else
set @sql = concat("create view sys.abc_view as select xxx from ", @dbname, ".", @tablename);
drop view if exists sys.abc_view;
prepare dynamic_view_stat from @sql;
execute dynamic_view_stat;
deallocate prepare dynamic_view_stat;
end if;
end//
DELIMITER ;
Schedule event / job.
Create a scheduled event to run stored procedure.
use sys;
SET GLOBAL event_scheduler = ON;
#SET GLOBAL event_scheduler = OFF;
CREATE EVENT event_call_sp_abc
ON SCHEDULE EVERY 6 hour
STARTS '2022-05-11 16:36:00'
ENDS '2032-01-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO
call sys.sp_abc();
#show events;
#DROP EVENT event_call_sp_abc;
Agg ist / group concat
SELECT name, group_concat(distinct attribute separator ', ') as attributes
FROM abc
Table partition
In MySQL deleting rows (millions of rows) from a table is very expensive and time consuming.
If possible, one way is to partition the table and truncate the partition that is not needed. E.g. Partition the below table by the day of load time, then truncate the partition that is not needed within a month.
Here it uses generated column 'LoadDay'.
create table if not exists test_table(
Element varchar(100),
Attribute varchar(100),
LoadTime datetime(6),
LoadDay int AS (extract(day from LoadTime))
)
PARTITION BY RANGE (LoadDay) (
PARTITION d1 VALUES LESS THAN (2),
PARTITION d2 VALUES LESS THAN (3),
PARTITION d3 VALUES LESS THAN (4),
...
PARTITION d30 VALUES LESS THAN (31),
PARTITION d31 VALUES LESS THAN (32)
)
;
To truncate a partition
alter table table_name truncate partition partition_name;
When selecting data from a table, if the partition is known, the performance can be improved by
select * from table partition partition_name where xxx
MySQL log file size too big
Under the data directory, there are files grows
servername-bin.00000x
Those bin.00000x files are operation log of the database for update, delete, etc.
The log is for data recovery and synchronization between master and slave nodes.
To remove the log files:
Run MySQL command line client, and run:
> RESET MASTER;
to clear log files.
To disable the log to save space, go to the my.cnf or my.ini config file
Comment out the line:
log-bin="servername-bin"
Restart service.
Note, if this is done before running the reset master command above, the command
won't be able to delete the log files because it won't know about the log files
once the line is commented out.
binlog files
MySQL by default keeps log in binlog files, e.g. binlog.000077
This seems to be different log to the log-bin files above? or actually the same?
This is for recovering database back to a previous state.
However, it doesn't use round-robin mechanism so it will eventually fill up all the space.
The delete/purge the log data before a date:
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
To purge a specific log file:
PURGE BINARY LOGS TO 'mysql-bin.010';
To purge all logs:
PURGE BINARY LOGS
Show process/queries and locks
SHOW PROCESSLIST;
JSON Table
To get a constant list of values in a query, e.g. a time table, or a sequence of numbers,
You can use a delimited string and use JSON table to split the string into table rows.
An example of getting day, hour for 28 days:
select DD,
HR,
DD * 24 + HR as Total_HR
from JSON_TABLE
(
CONCAT('[', '0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28', ']'), "$[*]"
COLUMNS(DD INT(20) PATH "$")
) AS D
cross join JSON_TABLE
(
CONCAT('[', '0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23', ']'), "$[*]"
COLUMNS(HR INT(20) PATH "$")
) AS H
order by DD, HR
mysql odbc connection
seems convert datatime to timestamp in the flow, which is from 1970 to 2038
The max time precision is 6 in mysql datetime(6)
The default osipi time format is MM/dd/yyyy, which is not accepted by mysql