一.字符集设置:
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索引快
对于>,<,>=,<=,!=,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)
查看更多关于mysql字符集索引函数存储过程 - mysql数据库栏目的详细内容...