mysql字符集索引函数存储过程 - mysql数据库栏目-自学php网

mysql字符集索引函数存储过程 - mysql数据库栏目

来源：自学PHP网时间：2015-04-16 10:50 作者：阅读:次

[导读] 一字符集设置:mysql的字符集包括字符集和校对规则，字符集用来定义mysql存储字符串的方式，校对规则用来定义比较字符串的方式字符集和校对规则是一对多的关系显示mysql可以使用的...

一.字符集设置:

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

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

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

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的字符集及校验规则:

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

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

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> 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索引快

对于>,<,>=,<=,!=,between,like 'pattern'的操作，则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 < 20 THEN ITERATE increment;
-> 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<string_len) DO
-> 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)

MySQLSQL剖析(SQLprofile) - mysql数据库栏目 - 自学php

mysql数据库读写分离 - mysql数据库栏目 - 自学php

子栏目

mysql字符集索引函数存储过程 - mysql数据库栏目

最新评论

添加评论

更多文章推荐

添加评论