Get The Type of a Variable in MySQL

Get the type of a variable in MySQL

You cannot determine the type of a variable in MySQL.

As an alternative, you can easily CAST() your variable in the type you desire:

@a = CAST(123 AS CHAR);

More information and examples about casting in the MySQL Manual:

11.9. Cast Functions and Operators

MySQL: How can we dynamically choose an appropriate type?

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

mysql function, how to refer to table field datatype

There is no way to do that in MySQL. DECLARE must statically declare a variable's type and size.

How to declare a variable in MySQL?

There are mainly three types of variables in MySQL:

User-defined variables (prefixed with @):
You can access any user-defined variable without declaring it or
initializing it. If you refer to a variable that has not been
initialized, it has a value of NULL and a type of string.
```
SELECT @var_any_var_name
```
You can initialize a variable using SET or SELECT statement:
```
SET @start = 1, @finish = 10;    
```
or
```
SELECT @start := 1, @finish := 10;

SELECT * FROM places WHERE place BETWEEN @start AND @finish;
```
User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary string,
or NULL value.
User-defined variables are session-specific. That is, a user
variable defined by one client cannot be seen or used by other
clients.
They can be used in SELECT queries using Advanced MySQL user variable techniques.
Local Variables (no prefix) :
Local variables needs to be declared using DECLARE before
accessing it.
They can be used as local variables and the input parameters
inside a stored procedure:
```
DELIMITER //

CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; //

DELIMITER ;

CALL sp_test(5);
```
If the DEFAULT clause is missing, the initial value is NULL.
The scope of a local variable is the BEGIN ... END block within
which it is declared.
Server System Variables (prefixed with @@):
The MySQL server maintains many system variables configured to a default value.
They can be of type GLOBAL, SESSION or BOTH.
Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.
To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.
```
SHOW VARIABLES LIKE '%wait_timeout%';

SELECT @@sort_buffer_size;
```
They can be set at server startup using options on the command line or in an option file.
Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:
```
-- Syntax to Set value to a Global variable:
SET GLOBAL sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000;

-- Syntax to Set value to a Session variable:
SET sort_buffer_size=1000000;
SET SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@local.sort_buffer_size=10000;
```

How to return field type from MySQL query?

You can use

SHOW FIELDS
FROM tableName where Field ='nameOfField'

This will return you result in format of

Field   Type    Null    Key     Default     Extra

Variables in mysql initialized with string datatype

No, to specify the type of your user defined variable you MUST declare it outside of the SELECT statement.

As you have already pointed out the MySQL documentation specifically mentions that you MUST declare the variable first:

9.4 User-Defined Variables

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.
Beginning with MySQL 8.0.22, a reference to a user variable in a prepared statement has its type determined when the statement is first prepared, and retains this type each time the statement is executed thereafter. Similarly, the type of a user variable employed in a statement within a stored procedure is determined the first time the stored procedure is invoked, and retains this type with each subsequent invocation.

This is very clear, either you formally declare the variable first, or it will be a string that is initialized with a value of NULL.

So just declare the variable first, you can declare variables within your inline SQL scripts, so don't try to fight it, either declare your variable first, or modify your query to use the variable as a string, either by casting amount to a string in the inner most query or cast @x to your numeric type in the comparison:

select 
    CASE
         WHEN (CAST(@x as SIGNED) != amount) THEN amount 
    END result, 
    CASE 
         WHEN (CAST(@x as SIGNED) != amount) THEN @x:=amount 
    END dummy 
from (select amount, @x:=0 x from a) q;

How to display the value of a variable at the commandline in MySQL?

Simply SELECT the variable like this:

SELECT @myId;

Here is the MySQL documentation on user-defined variables:

http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Get The Type of a Variable in MySQL