There are three types of MySQL stored procedure parameters: of in out inout

1. MySQL stored procedure parameters (in)
The MySQL stored procedure "in" parameter: similar to the C language function parameter value passing, this parameter may be modified internally by the MySQL stored procedure, but changes to the in type parameter are not visible to the caller (not visible).

 
drop procedure if exists pr_param_in; 
create procedure pr_param_in 
( 
in id int -- in  The type of  MySQL  Stored procedure parameter  
) 
begin 
if (id is not null) then 
set id = id + 1; 
end if; 
select id as id_inner; 
end; 
set @id = 10; 
call pr_param_in(@id); 
select @id as id_out; 
mysql> call pr_param_in(@id); 

+----------+
| id_inner |
+----------+
| 11 |
+----------+
mysql > select @id as id_out;
+--------+
| id_out |
+--------+
| 10 |
+--------+
As you can see, the user variable @id is passed in with a value of 10, and when the stored procedure is executed, the internal value is: 11 (id_inner), but the external value is still: 10 (id_out).

2. MySQL stored procedure parameters (out)
The MySQL stored procedure "out" parameter: passes a value from within the stored procedure to the caller. Inside the stored procedure, the parameter starts with null, whether or not the caller sets a value for the stored procedure parameter.

 
drop procedure if exists pr_param_out; 
create procedure pr_param_out 
( 
out id int 
) 
begin 
select id as id_inner_1; -- id  The initial value for the  null 
if (id is not null) then 
set id = id + 1; 
select id as id_inner_2; 
else 
select 1 into id; 
end if; 
select id as id_inner_3; 
end; 
set @id = 10; 
call pr_param_out(@id); 
select @id as id_out; 
mysql> set @id = 10; 
mysql> 
mysql> call pr_param_out(@id); 

+------------+
| id_inner_1 |
+------------+
| NULL |
+------------+
+------------+
| id_inner_3 |
+------------+
| 1 |
+------------+
mysql > select @id as id_out;
+--------+
| id_out |
+--------+
| 1 |
+--------+
As you can see, although we set the user-defined variable @id to 10, id always starts with null (id_inner_1) inside the stored procedure after passing @id to it. Finally, the id value (id_out = 1) is passed back to the caller.

3. MySQL stored procedure parameters (inout)
The MySQL stored procedure inout parameter is similar to out in that it can be passed from inside the stored procedure to the caller. The difference is that the caller can also pass a value to the stored procedure via the inout parameter.

 
drop procedure if exists pr_param_inout; 
create procedure pr_param_inout 
( 
inout id int 
) 
begin 
select id as id_inner_1; -- id  The value is the value passed in by the caller  
if (id is not null) then 
set id = id + 1; 
select id as id_inner_2; 
else 
select 1 into id; 
end if; 
select id as id_inner_3; 
end; 
set @id = 10; 
call pr_param_inout(@id); 
select @id as id_out; 
mysql> set @id = 10; 
mysql> 
mysql> call pr_param_inout(@id); 

+------------+
| id_inner_1 |
+------------+
| 10 |
+------------+
+------------+
| id_inner_2 |
+------------+
| 11 |
+------------+
+------------+
| id_inner_3 |
+------------+
| 11 |
+------------+
mysql >
mysql > select @id as id_out;
+--------+
| id_out |
+--------+
| 11 |
+--------+
As you can see from the results: we pass @id (10) to the stored procedure, which then returns the calculated value of 11 (id_inner_3) back to the caller. The MySQL stored procedure inout parameter behaves similar to the reference pass value in C language functions.

Use the example above: if you only want to pass data to the MySQL stored procedure, use the "in" type parameter; If you only return a value from the MySQL stored procedure, use the "out" type parameter. If you need to pass the data to the MySQL stored procedure, and then pass it back to us after some computation, you use the "inout" type parameter.