Stored procedures in mysql do not validate type?

Question

I have a stored file in my mysql that is in simple, it selects a table and filters by an id. Follow the store:

create
definer procedure fn_get_client_data(IN _email varchar(100), IN _id_loja int)
BEGIN
      SELECT
        cliente_nome,
        telefone,
        created_at,
      FROM lead_cliente
    WHERE
        email = `_email`
    AND
        id_loja = `_id_loja`;
END;

Turns out I did an incorrect implementation and passed the inverted parameters.

CALL backoffice.fn_get_customer_list(4, '[email protected]');

And yet the stored Procedure was executed bringing zero results. It is correct to bring zero results, but I would like to know:

Does it not do type validation? It was passed an integer where it should be a varchar and a varchar where it should be an integer. I tried to find this in the documentation but I didn’t find anything talking about typing? In datagrip it does not allow you to run this stored trial, so I imagine the IDE does this validation, but not the database itself.

Answer 1

I figured out what it was and how to solve it.

In mysql settings it is possible to set the

sql_mode="STRICT_ALL_TABLES";

When executing this command, stored procedures will validate the type of parameters. If you want this validation not to occur, the following command can be executed:

 sql_mode="";

Important! I recommend reading the Mysql documentation because the sql_mode accepts other parameters than STRICT_ALL_TABLES. Documentation: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html