MySQL: How can we dynamically choose an appropriate type?-CodePudding

Is there such an opportunity? Let's say, we have a range of integers. We loop over them and find out that they don't exceed 65535. So, now we know that SMALLINT would be a suitable type and are going to create a table with a column of the appropriate type! How can we do this?

I've tried various combinations like:
```
SET @ty := "INT";

CREATE TABLE tab (
  id @ty
);
```
but they all have failed and ended up complaining on @ty. So, the other question is whether we can keep in a server script @variable (as INT or as "INT")?

Thank you very much for your time!

CodePudding user response：

By loop over them and find out that they don't exceed 65535 ,if you mean the highest values does not exceed 65535, then we can use max() function to get the highest value. If you mean the number of values within the range, the count() function should be used instead. Next we can perform a condition check to determine which numeric type should be used. The rest is to create the wanted table based on the result from the condition check using PREPARED STATEMENT. To illustrate , we create a table and insert values to simulate the number range. Then use a procedure to create the intended table dynamically.

create table numbers (n int);
insert numbers values(1),(2),(70000);

delimiter //
drop procedure if exists create_table //
create procedure create_table()
begin
declare num_range int;
select max(n) from numbers into num_range; -- supposing you mean the highest values does not exceed 65535
If num_range<65536 then
    set @create_tb_stmt=concat('create table `tab` (id smallint);');
    else 
    set @create_tb_stmt=concat('create table `tab` (id int);');
end if;

drop table if exists `tab` ;
PREPARE stmt FROM @create_tb_stmt;
EXECUTE stmt;

end//
call create_table // -- call the procedure to make it happen