mysql字符串属性(字符集、大小写敏感、字符串数据类型等)

-----------------------------------------------------
二进制数据类型    非二进制数据类型     最大长度
binary                    char                            255
varbinary                varchar                        65535
tinyblob                tinytext                    255
blob                        text                            65535
mediumblob            mediumtext                16777215
longblog                longtext                    4294967295
-----------------------------------------------------

--char与varchar处理尾部空格的差异之处
create table t (c1 char(10), c2 varchar(10));
insert into t (c1, c2) values('abc      ', 'abc      ');
select c1, c2, char_length(c1), char_length(c2) from t;

--指定列character set与collation
create table mytbl
(
    utf8data varchar(100) character set utf8 collate utf8_danish_ci,
    sjisdata varchar(100) character set sjis collate sjis_japanese_ci
);

--character-set-server
--collation-server

----------------------正确设置客户端连接的字符集-------------------------

--配置文件
[mysql]
default-character-set=utf-8

--mysql命令行
set names 'utf8';
set names 'utf8' collate 'utf8-general-ci';

--编程接口:MySQL connectot/J, 应用程序连接URL
jdbc:mysql://localhost/cookbook?characterEncoding=UTF-8

----------------------串字母-------------------------

--定义字符串
'my string'
"my string"  /*ansi_quotes 模式启用时双引号内的字符解释为表名或列名*/

--使用十六进制数标记abcd
0x6162364
x'6162364'
X'6162364'
insert into t set binary_col = 0xdeadbeef;  /*sql中使用十六进制字符串指代二进制数值*/

--为字符串指定一个字符集解释器
_latin1 'abcd'  /*包含四个单字节字符的字符串*/
_ucs2 'abcd'  /*包含两个双字节字符的字符串*/

--包含单引号的字符串
select "I'm asleep";
select 'I''m asleep';
select 'I'm wide awake';

--包含双引号的字符串
select 'He said, "Boo!"';
select "He said, ""Bob!""";
select "And I said "Yikes!"";

--包含反斜线的字符串(特殊字符b,n,r,t,,)
select 'Install MySQL in c:mysql on Windows';

--使用十六进制数表示字符串
select 0x49276D2061736C656570;

----------------------检查一个字符串的字符集或字符排序-------------------------

--返回特定字符集和字符排序
select user(), charset(user()), collation(user());

--改变当前字符集影响普通字符串字符集和字符排序
set names 'latin1';
select charset('abc'), collation('abc');
set names latin7 collate 'latin7_bin';
select charset('abc'), collation('abc');

--返回二机制字符串
select charset(md5('a')), collation(md5('a'));
select charset(password('a')), collation(password('a'));

--转换二机制字符串结果大小写将失败
select upper(md5('a')), lower(md5('a'));

----------------------改变字符串的字符集或字符排序-------------------------

--改变字符集
set @s1 = "my string";
set @s2 = convert(@s1 using utf8);
select charset(@s1), charset(@s2);

--改变collation
set @s1 = 'my string';
set @s2 = @s1 collate latin1_spanish_ci;
select collation(@s1), collation(@s2);
select _latin1 'abc' collate utf8_bin; /*出错，Collation对于字符串的字符集必须是合法的*/

set @s1 = "my string";
set @s2 = convert(@s1 using utf8) collate utf8_spanish_ci;  /*正确，先转换字符集，再转换排序*/
select charset(@s1), collation(@s1), charset(@s2), collation(@s2);

--转换二进制字符串和非二进制字符串
set @s1 = 'my string';
set @s2 = convert(@s1 using binary);
set @s3 = convert(@s2 using utf8);
select charset(@s1), charset(@s2), charset(@s3);

--binary操作符产生二进制字符串
set @s1 = 'my string';
set @s2 = binary @s2; /*与convert(str using binary)等效*/
select charset(@s1), charset(@s2);

----------------------更改字符串字母的大小写-------------------------

--整体替换大小写
select thing, upper(thing), lower(thing) from limbs;

--局部修改大小写
select thing, concat(upper(left(thing, 1)), mid(thing, 2)) from limbs;

--自定义函数局部修改大小写
create function initial_cap(s varchar(255))
returns varchar(255) deterministic
return concat(upper(left(s, 1)), mid(s, 2));

select thing, initial_cap(thing) from limbs;

----------------------更改字符串字母的大小写失败处理情况-------------------------

--改变非二进制字符串大小写
set @s = 'aBcD';
select upper(@s), lower(@s);

--改变二进制字符串大小写
drop table if exists t;
create table t(b blob) select 'aBcD' as b;
select b, upper(b), lower(b) from t;  /*upper、lower改变二进制字符串大小写不生效*/

set @s = binary 'aBcD';
select @s, lower(@s), upper(@s);  /*MySQL4.1之前生效*/

select b, upper(convert(b using latin1)) as upper, lower(convert(b using latin1)) as lower from t;        /*先转换二进制字符串到非二机制字符串，再进行大小写转换*/
select charset(version()), charset(md5('some string'));
select upper(version());
select md5('some thing'), upper(convert(md5('some thing') using latin1));

----------------------控制字符串比较中的大小写敏感-------------------------

select 'cat' = 'cat', 'cat' = 'dpg';
select 'cat' != 'cat', 'cat' != 'dpg';
select 'cat' < 'linuxjishu/13830.html target=_blank class=infotextkey>awk', 'cat' < 'dpg';
select 'cat' between 'awk' and 'egret';

set @s1 = binary 'cat', @s2 = binary 'CAT';
select @s1 = @s2;        /*二进制字符串比较大小写敏感，结果不等*/
set @s1 = convert(@s1 using latin1) collate latin1_swedish_ci;
set @s2 = convert(@s2 using latin1) collate latin1_swedish_ci;
select @s1 = @s2;        /*转换成非二进制字符串，并指定字符集collation大小写不敏感，结果相等*/

/*latin1默认collation为latin1_swedish_ci*/
set @s1 = convert(@s1 using latin1); 
set @s2 = convert(@s2 using latin1);
select @s1 = @s2;   

set @s1 = _latin1 'cat', @s2 = _latin1 'CAT'; 
select @s1 = @s2;   

select @s1 collate latin1_general_cs = @s2 collate latin1_general_cs as '@s1 = @s2';

select _latin1 'cat' = binary 'CAT';

set @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
select @s1 = @s2, binary @s1 = @s2, @s1 = binary @s2;

--修改表定义，改变列大小写敏感模式
create table new
(
    id int unsigned not null auto_increment,
    article blob, /*大小写敏感*/
    primary key(id)
);

alter table news
modify article text charcter set utf8 collate utf_general_ci; /*大小写不敏感*/

----------------------使用sql模式进行模式匹配-------------------------

--消除可能存在的表冲突
drop table if exists metal;

--创建表
create table metal (name varchar(20));

--初始化表数据
insert into metal (name) values('copper'), ('gold'), ('iron'), ('lead'), ('mercury'), ('platinum'), ('silver'), ('tin');

--查询初始化数据
select * from metal;

--查询以特定字符开头的字符串
select name from metal where name like 'co%';

--查询以特定字符结尾的字符串
select name from metal where name like '%er';

--查询含有特定字符的字符串
select name from metal where name like '%er%';

--查询特定位置出现特定字符
select name from metal where name like '__pp%';

--查询不含有特定字符的字符串
select name from metal where name not like '%i%';

--Null值
select null like '%', null not like '%';

----------------------使用正则表达式进行模式匹配-------------------------

--模式字符与定义
模式字符                定义
^                                匹配字符串的开始部分
$                                匹配字符串的结束部分
.                                匹配任何字符（包括回车和新行）
[...]                        括号内任意一个字符
[^...]                    除了括号内所列字符之外的任意一个字符       
p1 | p2 | p3        p1、p2或p3中任意一个模式串
*                                匹配0或多个*之前的任何序列
+                                匹配1或多个+之前的任何序列
{n}                            n个{n}之前的任何序列
{m, n}                    最少m个，最多n个，{m, n}之前的任何序列

--查询以特定字符开头的字符串
select name from metal where name regexp '^co';

--查询以特定字符结尾的字符串
select name from metal where name regexp 'er$';

--查询含有特定字符的字符串
select name from metal where name regexp 'er';

--查询特定位置出现特定字符
select name from metal where name regexp '^..pp%';

--查询不含有特定字符的字符串
select name from metal where name not like '%i%';

--posix字符集定义正则表达式
posix类                        匹配定义
[:alnum:]                    字符和数字
[:alpha:]                    字母
[:blank:]                    空格或制表符(tab)
[:cntrl:]                    控制符
[:digit:]                    数字
[:graph:]                    图形符号(不包括空格)
[:lower:]                    小写字母
[:print:]                    图形符号(包括空格)
[:punct:]                    标点符号
[:space:]                    空格、制表符、换行、回车换行
[:upper:]                    大写字母
[:xdigit:]                十六进制符(0-9, a-f, A-F)

--检查是否含有十六进制字符
select name, name regexp '[[:xdigit:]]' from metal;

--选择性匹配
select name from metal where name regexp '^[aeiou]|er$';

--对正则表达式进行分组
select '0m' regexp '^[[:digit:]]+|[[:alpha:]]+$';  /*以数字开头或字母结尾*/
select '0m' regexp '^([[:digit:]]+|[[:alpha:]]+)$';  /*完全由数字或者完全由字母组成*/

--Null值
select null regexp '.*', null not regexp '.*';

----------------------模式匹配大小写问题-------------------------

select 'a' like 'A', 'a' regexp 'A';
select 'a' regexp '[[:lower:]]', 'a' regexp '[[:upper:]]';

set names latin1;
set @s = 'a' collate latin1_general_cs;
select @s like 'A', @s regexp 'A';

set @s = 'a', @s_cs = 'a' collate latin1_general_cs;
select @s regexp '[[:upper:]]', @s_cs regexp '[[:upper:]];'

----------------------分割或者串联字符串-------------------------

--取得左侧、中间、右侧字串
select name, left(name, 2), mid(name, 3, 1), right(name, 3) from metal;

--取得字串substring()
select name, substring(name, 4), mid(name, 4) from metal;

--取得字串substring_index()
select name, substring_index(name, 'r', 1), substring_index(name, 'i', -1);  /*正数从左到右，负数从右到左*/
select name from metal where left(name, 1) >= 'n';

--拼接字符串concat()
select concat('Hello', user(), ', welcome to MySQL!') as greeting;
select concat(name, ' ends in "d": ', if(right(name, 1)='d', 'yes', 'no')) as 'ends in "d"?' from metal;
update metal set name = concat(name, 'ide');
select name from metal;

update tbl_name set set_col = if(set_col is null, val, concat(set_col, ', ', val));

update metal set name = left(name, char_length(name) - 3);
select name from metal;

----------------------查询字串-------------------------

select name, locate('in', name), locate('in', name, 3) from metal;

----------------------使用fulltext查询-------------------------

--创建表
create table kjv
(
    bsect enum('0', 'N') not null,
    bname varchar(20) not null,
    bnum tinyint unsigned not null,
    cnum tinyint unsigned not null,
    vnum tinyint unsigned not null,
    vtext text not null
) engine = myisam;

--导入初始化数据
load data local infile 'kjv.txt' into table kjv;

--添加全文索引
alter table kjv add fulltext(vtext);

--查询'Mizraim'一共出现了多少次
select count(*) from kjv where match(vtext) against('Mizraim');

--查询'Mizraim'具体出现在什么地方
select bname, cnum, vnum, vtext from kjv where match(vtext) against('Mizraim')G
select bname, cnum, vnum, vtext from kjv where match(vtext) against('search string') order by bnum, cnum, vnumG

select count(*) from kjv where match(vtext) against('Abraham') and bsect = 'N';
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews';
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews' and cnum = 11;

alter table kjv add index(bnum), add index(cnum), add index(vnum);
select count(*) from kjv where match(vtext) against('Abraham');
select count(*) from kjv where match(vtext) against('Abraham Sarah');
select count(*) from kjv where match(vtext) against('Abraham Sarah Ishmael Isaac');

alter table tbl_name add fulltext(col1, col2, col3);
select ... from tbl_name where match(col1, col2, col3) against('search string');

----------------------用短语来进行fulltext查询-------------------------

select count(*) from kjv where match(vtext) against('God');
select count(*) from kjv where match(vtext) against('sin');

select count(*) as 'total verses',
        count(if(vtext like '%God%', 1, null)) as 'Verses containing "God"',
        count(if(vtext like '%sin%', 1, null)) as 'Verses containing "sin"'
from kjv;

--修改配置文件/etc/my.cnf 或者mysqlhome/my.ini
[mysqld]
ft_min_world_len=3;

--重启服务器启用新的设置
repair table kjv quick;

select count(*) from kjv where match(vtext) against('God');
select count(*) from kjv where match(vtext) against('sin');

----------------------要求或静止fulltext搜索单词-------------------------

select count(*) from kjv where match(vtext) against('David Goliath');
select count(*) from kjv where match(vtext) against('+David +Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('+David -Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('-David +Goliath' in boolean mode);
select count(*) from kjv where match(vtext) against('whirl*' in boolean mode);

----------------------用fulltext索引来执行词组查询-------------------------

select count(*) from kjv where match(vtext) against('still small voice');
select count(*) from kjv where match(vtext) against('"still small voice"' in boolean mode);