MySQL Advanced 1

1. MySQL encoding settings

1. Check the encoding of the MySQL service

show variables like 'char%';

2. Modify the encoding of the data table

alter table test character set utf8;

3. Modify a certain part of the data table The encoding of a field name

alter table test change code code varchar(32) character set utf8 not null;

2. Session variables and global variables

1. Session variables

show session variables;

2. Session variables Fuzzy query

show session variables like 'auto%';

3. Set reply

set @@session.autocommit ='off';

4. View global variables

show global variables;

3. Stored procedures

1), stored procedures enhance the functionality and flexibility of the SQL language

2), stored procedures allow standard components to be programmed

3), stored procedures can achieve faster execution speed

4), stored procedures can reduce network traffic

5), stored procedures can be fully utilized as a security mechanism

1. Use:

1), first select the database

2), change the delimiter: do not use; as the mark of the end of execution.

For example:

delimiter $$;

create procedure p_hello()  
begin  
select 'hello';  
select 'world';  
end  
$$;

3), change the delimiter back to

delimiter ;

4), call the above stored procedure

call p_hello;

2, define the stored procedure locally Variable

1), first type of variable assignment

create procedure p_vartest()  
begin  
declare a varchar(20) default 'abc';  
select a;  
end  
$$;

2), second type of variable assignment

create procedure p_vartest2()  
begin  
declare inta int;  
set inta = 10;  
select inta;  
end  
$$;

3), stored procedure parameter passing

create procedure p_vartest3(in p_int int)  
begin  
select p_int;  
set p_int = p_int + 1;  
select p_int;  
end  
$$;

a. Define a variable

set @p_int = 3;

b. Call the stored procedure

call p_vartest3(@p_int);

d. View Changes in variables in the database

The variables in the database have not been modified, which means that the stored procedure only assigns values ​​to the variables.

The above is the content of MySQL Advanced One. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!