mysql字符集索引函数存储过程_MySQL

一.字符集设置:

mysql的字符集包括字符集和校对规则，字符集用来定义mysql存储字符串的方式，校对规则用来定义比较字符串的方式.字符集和校对规则是一对多的关系.

显示mysql可以使用的字符集：

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.08 sec)

显示某种字符集的校对规则:

mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.03 sec)

校对规则命名约定:校对规则=字符集名+语言名+(_ci,_cs,_bin)结束.

_ci对字符的大小写不敏感

_cs对字符大小写敏感

_bin比较的是基于字符编码的值而与language无关

对于gbk的两个校验规则：gbk_chinese_ci和 gbk_bin，前者对大小写不敏感，后者按编码的值进行比较，对大小写敏感：

mysql> select case when 'A' collate gbk_chinese_ci = 'a' collate gbk_chinese_
then 1 else 0 end;
+----------------------------------------------------------------------------
| 1 |
+----------------------------------------------------------------------------
1 row in set (0.06 sec)

然而:

mysql> select case when 'A' collate gbk_bin = 'a' collate gbk_bin
-> then 1 else 0 end;
+-----------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

显示database的字符集及校验规则:

mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | gb2312 |
+------------------------+--------+
1 row in set (0.08 sec)
mysql> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name | Value |
+--------------------+-------------------+
| collation_database | gb2312_chinese_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)

字符集的级别:服务器级别，数据库级别，表级别，字段级别.

设置数据库字符集的基本规则:

a.如果指定了字符集和校验规则，则使用指定的；

b.如果指定了字符集但没有指定校验规则，则使用字符集默认的校验规则；

c.如果指定了校验规则但未指定字符集，则使用与该校验规则绑定的字符集；

d.如果没有指定字符集和校验规则，则使用服务器的字符集和校验规则.

表的字符集设定 same with the above：

mysql> show create table z1 \G;
*************************** 1. row ***************************
Table: z1
Create Table: CREATE TABLE `z1` (
`id` varchar(11) character set gb2312 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.23 sec)

修改z1表的字符集:

mysql> alter table z1 character set gbk;
Query OK, 0 rows affected (1.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table z1 \G;
*************************** 1. row ***************************
Table: z1
Create Table: CREATE TABLE `z1` (
`id` varchar(11) character set gb2312 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> insert into z1 values('有钱先生就是我');
Query OK, 1 row affected (0.09 sec)
mysql> select * from z1;
+----------------+
| id |
+----------------+
| 有钱先生就是我 |
+----------------+
1 row in set (0.03 sec)

中文问题就解决了.

二.索引的一点小知识:

项目中有时候会有些select查询特别慢，后来大牛加了个索引，速度超快，用的就是索引,

mysql的存储引擎MyISAM和InnoDB默认使用的都是BTREE索引.

create index x on table y(A,B);

在y表,A,B字段建立x索引

a.最适合索引的列是where子句中的列，而不是select中列;

b.使用唯一索引，列的基数越大，索引的效果越好;

c.索引会占用额外的磁盘空间，莫滥用，否则会降低写操作的性能.

对于使用=或操作符的比较，hash索引会比btree索引快

对于>,=,

三.存储过程及自定义函数(面试必备):

1.delimiter一个函数或sp终结符;

2.create一个function或sp;

3.将终结符替换成mysql使用的';';

4.直接select或call调用.

mysql> delimiter $$
mysql> CREATE FUNCTION myFunction2
-> (
-> in_string VARCHAR(255),
-> in_find_str VARCHAR(20),
-> in_repl_str VARCHAR(20)
-> )
-> RETURNS VARCHAR(255)
-> BEGIN
-> DECLARE l_new_string VARCHAR(255);
-> DECLARE l_find_pos INT;
-> SET l_find_pos=INSTR(in_string,in_find_str);
->
-> IF (l_find_pos>0) THEN
-> SET l_new_string=INSERT(in_string,l_find_pos,LENGTH(in_find_s
tr),in_repl_str);
-> ELSE
-> SET l_new_string=in_string;
-> END IF;
-> RETURN(l_new_string);
-> END $$
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> select myFunction2('ABC','A','Z');
+----------------------------+
| myFunction2('ABC','A','Z') |
+----------------------------+
| ZBC |
+----------------------------+
1 row in set (0.00 sec)

另一个demo:

mysql> delimiter $$
mysql> CREATE FUNCTION myFunction3(
-> in_title VARCHAR(4),
-> in_gender CHAR(1),
-> in_firstname VARCHAR(20),
-> in_middle_initial CHAR(1),
-> in_surname VARCHAR(20))
->
-> RETURNS VARCHAR(60)
-> BEGIN
-> DECLARE l_title VARCHAR(4);
-> DECLARE l_name_string VARCHAR(60);
->
-> IF ISNULL(in_title) THEN
-> IF in_gender='M' THEN
-> SET l_title="Mr";
-> ELSE
-> SET l_title="Ms";
-> END IF;
-> END IF;
->
-> IF ISNULL(in_middle_initial) THEN
-> SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',in_surnam
e);
-> ELSE
-> SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',
-> in_middle_initial,' ',in_surname);
-> END IF;
->
-> RETURN(l_name_string);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> select myFunction3('Mrs','M','First','M','Last');
-> select myFunction3('Mrs','M','First','M','Last')$$
+-------------------------------------------+
| myFunction3('Mrs','M','First','M','Last') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set (0.01 sec)

+-------------------------------------------+
| myFunction3('Mrs','M','First','M','Last') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set (0.03 sec)

mysql> delimiter ;
mysql> select myFunction3(NULL,'M','First','M','Last');
+------------------------------------------+
| myFunction3(NULL,'M','First','M','Last') |
+------------------------------------------+
| Mr First M Last |
+------------------------------------------+
1 row in set (0.00 sec)

红色部分，因为没有delimiter ;，从而以分号结尾的调用没有成功，使用$$就可以.

创建完函数或过程，一定delimiter ;

当然可以指定别的分隔符:

mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction4(rush_ship INT(10)) RETURNS DECIMAL(10,2)
-> BEGIN
-> DECLARE rush_shipping_cost DECIMAL(10,2);
-> CASE rush_ship
-> WHEN 1 THEN
-> SET rush_shipping_cost = 20.00;
-> WHEN 2 THEN
-> SET rush_shipping_cost = 15.00;
-> WHEN 3 THEN
-> SET rush_shipping_cost = 10.00;
-> ELSE
-> SET rush_shipping_cost = 0.00;
-> END CASE;
-> RETURN rush_shipping_cost;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select myFunction4(2);
+----------------+
| myFunction4(2) |
+----------------+
| 15.00 |
+----------------+
1 row in set (0.00 sec)

一个简单的sp:

mysql> DELIMITER //
mysql> CREATE PROCEDURE tom2.myProc (IN in_count INT)
-> BEGIN
-> DECLARE count INT default 0;
-> increment: LOOP
-> SET count = count + 1;
-> IF count -> END IF;
-> IF count > in_count THEN LEAVE increment;
-> END IF;
-> END LOOP increment;
-> SELECT count;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql> call tom2.myProc(5);
-> call tom2.myProc(5)//
+-------+
| count |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)

查看sp(stored procedure)状态:

mysql> show procedure status like 'myProc' \G;
*************************** 1. row ***************************
Db: tom2
Name: myProc
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-10-12 23:18:28
Created: 2014-10-12 23:18:28
Security_type: DEFINER
Comment:
1 row in set (0.00 sec)
为sp添加characteristics特征值:

1.LANGUAGE SQL:说明该sp由sql编写;

2.CONTAINS SQL(包含sql)|NO SQL|READS SQL DATA|MODIFIES SQL DATA;

3.SQL SECURITY{DEFINER|INVOKER},指定sp的调用者权限;

4.COMMENT:sp的说明

实例:

mysql> ALTER PROCEDURE tom2.myProc SQL SECURITY INVOKER
-> COMMENT "Tom's procedure";
Query OK, 0 rows affected (0.00 sec)
mysql> show procedure status like 'myProc' \G;
*************************** 1. row ***************************
Db: tom2
Name: myProc
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-10-12 23:27:54
Created: 2014-10-12 23:18:28
Security_type: INVOKER
Comment: Tom's procedure
1 row in set (0.02 sec)

一个完整的demo：

mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, sa
lary, City, Description)
-> values (1,'Jason', 'Martin', '19960725', '20060725', 1234.56, 'Toron
to', 'Programmer'),
-> (2,'Alison', 'Mathews', '19760321', '19860221', 6661.78, 'Vancouve
r','Tester'),
-> (3,'James', 'Smith', '19781212', '19900315', 6544.78, 'Vancouve
r','Tester'),
-> (4,'Celia', 'Rice', '19821024', '19990421', 2344.78, 'Vancouve
r','Manager'),
-> (5,'Robert', 'Black', '19840115', '19980808', 2334.78, 'Vancouve
r','Tester'),
-> (6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York'
, 'Tester'),
-> (7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York'
, 'Manager'),
-> (8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver
', 'Tester');
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> DELIMITER //
mysql> CREATE PROCEDURE merge_employee (IN old_id INT, IN new_id INT, OUT error
VARCHAR(100))
-> SQL SECURITY DEFINER
-> COMMENT 'This is the comment'
-> BEGIN
-> DECLARE old_count INT DEFAULT 0;
-> DECLARE new_count INT DEFAULT 0;
-> DECLARE addresses_changed INT DEFAULT 0;
->
-> ## check to make sure the old_id and new_id exists
-> SELECT count(*) INTO old_count FROM employee WHERE id = old_
id;
-> SELECT count(*) INTO new_count FROM employee WHERE id = new_
id;
->
-> IF !old_count THEN
-> SET error = 'old id does not exist';
-> ELSEIF !new_count THEN
-> SET error = 'new id does not exist';
-> ELSE
-> UPDATE employee SET id = new_id WHERE id = old_id;
-> SELECT row_count() INTO addresses_changed;
->
-> DELETE FROM employee WHERE id = old_id;
->
-> SELECT addresses_changed;
->
-> END IF;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call merge_employee(1,4,@error);
+-------------------+
| addresses_changed |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
mysql> select @error;
+--------+
| @error |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

一个简单的while循环,计算前四个字母的ASCII码:

mysql> delimiter $$

mysql> CREATE FUNCTION myFunction5(in_string varchar(80) )
-> RETURNS VARCHAR(256)
-> NO SQL
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> DECLARE string_len INT;
-> DECLARE out_string VARCHAR(256) DEFAULT '';
-> SET string_len=length(in_string);
-> WHILE (i -> SET out_string=CONCAT(out_string,ASCII(substr(in_string,i,1
)),' ');
-> SET i=i+1;
-> END WHILE;
-> RETURN (out_string);
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select myFunction5('ABCDE');
+----------------------+
| myFunction5('ABCDE') |
+----------------------+
| 65 66 67 68 |
+----------------------+
1 row in set (0.00 sec)

另一个demo(拼sql语句)：

mysql> delimiter $$
mysql> CREATE PROCEDURE sp_customer_search
-> (in_Description VARCHAR(30
-> in_contact_surname VARCHA
-> in_contact_firstname VARC
-> in_city VARCHAR(10))
-> BEGIN
-> DECLARE l_where_clause VARCH
-> IF in_Description IS NOT NUL
-> SET l_where_clause=CONCA
-> ' description="',in_D
-> END IF;
-> select l_where_clause;
-> IF in_contact_surname IS NOT
-> IF l_where_clause'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' last_name="',in_con
-> END IF;
-> select l_where_clause;
-> IF in_contact_firstname IS N
-> IF l_where_clause'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' first_name="',in_co
-> END IF;
-> select l_where_clause;
-> IF in_city IS NOT NULL THEN
-> IF l_where_clause'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' city="',in_city,'"'
-> END IF;
-> select l_where_clause;
-> SET @sql=CONCAT('SELECT * FR
-> select @sql;
-> PREPARE s1 FROM @sql;
-> EXECUTE s1;
-> DEALLOCATE PREPARE s1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call sp_customer_search_dyn('Tester','Smith','James','Vancouver');
+----------------------------+
| l_where_clause |
+----------------------------+
| WHERE description="Tester" |
+----------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------+
| l_where_clause |
+---------------------------------------------------+
| WHERE description="Tester" AND last_name="Smith" |
+---------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------------------------+
| l_where_clause |
+---------------------------------------------------------------------------+
| WHERE description="Tester" AND last_name="Smith" AND first_name="James" |
+---------------------------------------------------------------------------+
1 row in set (0.02 sec)
+-------------------------------------------------------------------------------
------------------+
| l_where_clause
|
+-------------------------------------------------------------------------------
------------------+
| WHERE description="Tester" AND last_name="Smith" AND first_name="James" AND
city="Vancouver" |
+-------------------------------------------------------------------------------
------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------------------------------
-----------------------------------------+
| @sql
|
+-------------------------------------------------------------------------------
-----------------------------------------+
| SELECT * FROM employee WHERE description="Tester" AND last_name="Smith" AND
first_name="James" AND city="Vancouver" |
+-------------------------------------------------------------------------------
-----------------------------------------+
1 row in set (0.06 sec)
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| id | first_name | last_name | start_date | end_date | salary | city
| description |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver
| Tester |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)

sp还可以捕获错误做出正确的处理:

mysql> delimiter $$
mysql> CREATE PROCEDURE myProc11
-> (in_first_name VARCHAR(30),
-> in_last_name VARCHAR(30),
-> in_city VARCHAR(30),
-> in_description VARCHAR(10),
-> OUT out_status VARCHAR(30))
-> MODIFIES SQL DATA
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR 1406
-> SET out_status="desc is to long";
->
-> SET out_status='OK';
-> INSERT INTO employee
-> (first_name,last_name,city,description)
-> VALUES
-> (in_first_name,in_last_name,in_city,in_description);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> set @myMessage = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call myProc11('Jason','Martin','ddddddddddddddd','New desc',@myMessage);
Query OK, 0 rows affected (0.27 sec)
mysql> select @myMessage;
+-----------------+
| @myMessage |
+-----------------+
| desc is to long |
+-----------------+
1 row in set (0.00 sec)