MYSQL – Run a query based on the results of a query

information-schemaMySQL

Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written.

select @tbl:=column_name as'Table',
       ( select max(length(@tbl)) 
         from project) as 'used_length', 
       character_maximum_length as 'defined length'
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'project'
  and column_name = 'announcement_notes'
group by column_name, 
         character_maximum_length
order by 1

Unfortunately, it's not running the max(length) query – it is returning the length of the label – in this case announcement_notes is 18 characters long.

Table              used_length  defined_length
-----------------  ------------ ---------------
announcement_notes 18           2000

[if that wasn't clear – the return from the query is announcement_notes, 18, 2000).

however, checking the actual length
select max(length(announcement_notes)) from project
returns 404 characters.

Any help would be greatly appreciated.

Best Answer

Prepared Statement is the answer for this.

set @stmt1= concat( 'select ', @var, ' from ', @vardb, ' where ', @varcond);
prepare stmt from @stmt1;
execute stmt;

For getting @tbl used properly,

   concat( 'select max(length(', @tbl, ')' )

will get it. But to select required columns from information_schema, you need to run a query first followed by coining this prepared statement and then execute the same.

Related Question