Обсуждение: insert into table
hi
may i know why the below mysql is not inserting into table analyze_checks
insert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )
(select
'test' as dbname,
table_name,
frag_ratio,
days,
needs_optimization,
needs_analyzing
from
(select
table_name,
cast(frag_ratio as decimal(5,2)) as frag_ratio,
days,
case when frag_ratio > 1 then 'Yes' else 'No' end as needs_optimization,
case when days > -1 then 'Yes' else 'No' end as needs_analyzing
from (
select
t.ENGINE,
concat(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as table_name,
round(t.DATA_FREE/1024/1024, 2) as data_free,
(t.data_free/(t.index_length+t.data_length)) as frag_ratio,
datediff(now(), last_update) as days
FROM information_schema.tables t
left join mysql.innodb_table_stats s on t.table_name=s.table_name
WHERE DATA_FREE > 0 ORDER BY frag_ratio DESC )d ) d
where needs_optimization='Yes' or needs_analyzing='Yes');
(select
'test' as dbname,
table_name,
frag_ratio,
days,
needs_optimization,
needs_analyzing
from
(select
table_name,
cast(frag_ratio as decimal(5,2)) as frag_ratio,
days,
case when frag_ratio > 1 then 'Yes' else 'No' end as needs_optimization,
case when days > -1 then 'Yes' else 'No' end as needs_analyzing
from (
select
t.ENGINE,
concat(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as table_name,
round(t.DATA_FREE/1024/1024, 2) as data_free,
(t.data_free/(t.index_length+t.data_length)) as frag_ratio,
datediff(now(), last_update) as days
FROM information_schema.tables t
left join mysql.innodb_table_stats s on t.table_name=s.table_name
WHERE DATA_FREE > 0 ORDER BY frag_ratio DESC )d ) d
where needs_optimization='Yes' or needs_analyzing='Yes');
this is a PostgreSQL mailing list, not a MySQL mailing list
On Fri, May 28, 2021 at 9:22 PM Yambu <hyambu@gmail.com> wrote:
may i know why the below mysql is not inserting into table analyze_checksinsert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )(select
Since I don't have the benefit of seeing an error message I'm going to presume the parentheses surrounding the select query are being rejected.
David J.
Hi David
There is no error, it just says zero rows affected, but when i run select it brings results
On Sat, May 29, 2021 at 6:29 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, May 28, 2021 at 9:22 PM Yambu <hyambu@gmail.com> wrote:may i know why the below mysql is not inserting into table analyze_checksinsert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )(selectSince I don't have the benefit of seeing an error message I'm going to presume the parentheses surrounding the select query are being rejected.David J.