mysql索引有字符集_mysql数据库字符集和索引

最新推荐文章于 2023-07-29 10:15:22 发布

林语堂表弟

最新推荐文章于 2023-07-29 10:15:22 发布

阅读量326

点赞数

文章标签： mysql索引有字符集

本文链接：https://blog.csdn.net/weixin_32655655/article/details/113300078

版权

#什么是字符集

#字符集：是一个计算机支持的所有抽象字符的集合。

#字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

编码：把人类可以识别的信息转化为机算计认识的0和1

解码：把机算计存储的0和1转化为人类可以识别的信息

乱码：编码解码的过程，导致一些数据不能解析

ASCII：美国

gbk 中国,一个汉字占用2个字节，#范围大于utf8

gbk2312#范围大于gbk

utf8一个汉字占用3个字节

utf8mb4一个汉字占用4个字节(utf8mb4不能转化为utf8，因为utf8不支持表情符号)

shift-jis日本

Euc-kr韩国

Unicode万国码(翻译官)

json 翻译官-- java python php

字符集修改要求：包含关系才可以修改(#小的可以转化成大的，并且是兼容关系)

ASCII码可以转化成任意编码

万国码可以转化成任意语言

#查看centos字符集,字符集与SQL语句是否支持大小写有关

mysql> show charset;

+----------+-----------------------------+---------------------+--------+

+----------+-----------------------------+---------------------+--------+

#查看校验规则

mysql> show collation;

+--------------------------+----------+-----+---------+----------+---------+

#校验规则

1.ci不区分大小写，SQL语句与SQL数据(字符集修改为ci的话，查找已存在的数据库名还是区分大小写，创建新库表不区分大小写，导致相同字段等不能添加)(utf8_general_ci)

2.cs区分大小写

3.bin区分大小写(utf8_bin)

#设置字符集

1.终端(小地球)

2.命令行

-------------------C7中设置字符集(#反馈中文)

临时

LANG=zh_CN.UTF-8

永久

vim /etc/locale.conf

LANG=zh_CN.UTF-8

-------------------CS6设置字符集

vim /etc/sysconfig/i18n

LANG=zh_CN.UTF-8

3.数据库字符集的修改(默认是拉丁)

1)预编译cmake阶段

cmake .

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

2)配置文件vim /etc/my.cnf

character_set_server=utf8

3)SQL语句

#库

create database xx charset utf8;

show create database xx;(查看数据库字符集)

库下建表，#库的字符集就是表的字符集

create table tb1(id int);

show create table tb1;((查看数据库中表的字符集))

修改库的字符集(#注意字符集的范围大小)

alter database xx charset utf8;

mysql> show create database xx;#实时查看建库字符集

批量修改库的字符集

1.可以用循环

alter database % utf8;(不能直接使用SQL语句批量修改数据库字符集)

mysql -uroot -p1 -e 'alter database $xx charset utf8'

2.数据导出(-A导出所有库，-B导出指定库)

mysqldump -uroot -p123 -B tb3 > a.sql

:%s#DEFAULT CHARACTER SET latin1#DEFAULT CHARACTER SET utf8#g

### drop database tb3;

mysql -uroot -p123 < a.sql

#表

指定字符集建表

create table xx(id int) charset utf8mb4;

show create table xx;

修改表的字符集(#属性)

alter table xx charset utf8;

show create table xx;

#修改字符集

mysql> alter table tb1 charset gbk;

mysql> alter table tb1 charset utf8;

mysql> alter table tb1 charset utf8_bin;

ERROR 1115 (42000): Unknown character set: 'utf8_bin'

mysql> show charset;

+----------+

| Charset |

+----------+

| big5

| dec8

| cp850

| hp8

| koi8r

| latin1

| latin2

| swe7

| ascii

| ujis

| sjis

| hebrew

#不能添加括号内容

mysql> show create table tb4(id int);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id int)' at line 1

mysql> show create table tb4;

| Table | Create Table

| tb4 | CREATE TABLE `tb4` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

#create database if not exists xx;

索引

#索引

1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。

2.索引让获取的数据更有目的性，从而'提高数据库检索数据的性能'。

#索引的种类

1.BTREE：

B+tree 叶节点有快速通道，速度更快一点

B*tree 枝节点叶节点都有快速通道，速度更快

2.HASHHASH索引(memery存储引擎才支持)

3.FULLTEXT:全文索引(myisam存储引擎才支持)速度较慢

4.RTREER树索引

#根据磁盘吞吐量(IO)判断索引检索速度

select * from table where id=38;

#BTREE

根节点

枝节点

叶节点#存储真实数据

索引根据算法分类

索引：

是建立在数据库'表的字段上面的'，当where条件后面'接的字段'有索引的时候，'会提高数据获取的速度'

1.主键索引(聚集索引)(Btree)

#创建表的时候创建主键索引(主键属性就是主键索引，因为有primary key)

creat table test(id int primary key auto_increment comment '学号')

create table test1(id int auto_increment,primary key(id));

#查看索引(Key_name==PRIMARY,主键只显示PRIMARY)(有索引才会显示)

show index from test；

+-------+------------+----------+--------------+-------------+-----------+--

# 表索引名联合索引的顺序字段名

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 'tb4' | 0 | 'PRIMARY' | 1 | idd | 'A' | 0 | NULL | NULL | | 'BTREE' | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

#还可以用desc查看索引类型

mysql> desc test;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | 'PRI' | 0 | |

+-------+---------+------+-----+---------+-------+

#show

mysql> show create table test;

| Table | Create Table

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

UNIQUE KEY `id` (`id`) #

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#在已存在的表添加主键索引(名字)

1).给指定列增加属性

alter table test1 add primary key pri_id(id);

show index from test1;

desc test1;

2).修改列属性

alter table xx modify id int primary key auto_increment;

#删除主键索引

mysql> alter table test3 drop primary key;

mysql> alter table cs drop index primary key;

ERROR 1064 (42000):

#伪主键

mysql> desc test3;

mysql> alter table test3 add primary key(id);

mysql> desc test3;

mysql> alter table test3 drop primary key;

mysql> desc test3;

mysql> alter table test3 add unique key 1_key(id)

mysql> desc test3;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

+-------+---------+------+-----+---------+-------+

mysql> alter table test3 add name varchar(4);

mysql> desc test3;

mysql> alter table test3 add unique key 2_key(name);

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | | #

+-------+------------+------+-----+---------+-------+

mysql> alter table test3 drop primary key;

ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

+-------+------------+------+-----+---------+-------+

mysql> alter table test3 add primary key(id);

mysql> desc test3;

mysql> alter table test3 drop primary key;

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | | #

+-------+------------+------+-----+---------+-------+

mysql> alter table test3 add unique key(id); #该列添加了唯一键，显示pri

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

+-------+------------+------+-----+---------+-------+

mysql> alter table test3 add primary key(a4)； #另一列添加主键(主键飘移)

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | UNI | 0 | |

| age | varchar(6) | YES | UNI | NULL | |

| a4 | int(11) | NO | PRI | 0 | |

+-------+------------+------+-----+---------+-------+

2.唯一键索引(#该列不能有重复数据)(Btree)(游戏名)----------------------

#创建表的时候创建唯一键索引

create table test2(id int unique key not null auto_increment comment '学号');

#在已存在的表添加唯一键索引(name字段要存在,uni_key不能重复)

alter table test2 add name int;

alter table test3 add unique key(a4)；#不给唯一键名字，默认为字段名

alter table test2 add unique key uni_key(name); #给唯一键名字

#modify，使用modify可以修改主键，但是不能修改唯一键

alter table xx modify id int primary key auto_increment;

alter table xx modify a5(unique key);

ERROR 1064 (42000):

mysql> show index from test2; (#可以看到key的名字)

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| test | 0 | 'id' | 1 | 'id' | A | 0 | NULL | NULL | | 'BTREE' | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show index from test3\G

*************************** 1. row ***************************

Table: test3

Non_unique: 0

Key_name: 1_key#唯一键的名字，删除的时候使用

Seq_in_index: 1

Column_name: id#字段名

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

mysql> desc test2; #不能看到key的名字

+-------+---------+------+-----+---------+----------------+

+-------+---------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | int(11) | YES | UNI | NULL | |

+-------+---------+------+-----+---------+----------------+

mysql> show create table test;

| Table | Create Table

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

UNIQUE KEY `id` (`id`) #唯一键名，字段名

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |#字符集

#判断一列能否做唯一建索引

1)查看指定列数据总数(数据数)

select count(name) from city;

去重查看指定列数据总数

select distinct(name) from city;

mysql> select count(distinct(name)) from city;

2)创建唯一键索引

alter table test2 add unique key pri_id(name);

desc test2;

#注意：创建唯一建索引或主键索引的列不能有重复数据

3.普通索引(辅助索引)，辅助查询(可以在已存在的索引字段中添加普通索引,但是索引名肯定是不能重复的)-------------------------------------------------

#已存在的表才能创建普通索引

alter table city add index in_name(name);

create index innn_a4 on test3(a6);

mysql> show index from test3\G

*************************** 7. row ***************************

Table: test3

Non_unique: 1

Key_name: in_na#索引名

Seq_in_index: 1

Column_name: a5

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| age | varchar(6) | YES | UNI | NULL | |

| a4 | int(11) | NO | UNI | 0 | |

| a5 | int(11) | YES | MUL | NULL | |#mul普通索引

+-------+------------+------+-----+---------+-------+

4.全文索引(一般用在文章里，针对一句话)----------------

mysql> create table txt(id int,bookname varchar(12),wenzhang text,fulltext(wenzhang));

mysql> select * from txt where match(wenzhang) against('查询完整的内容');

#主键索引 > 唯一键索引 > 普通索引优先级(优先级高的有效显示)

#主键索引只能创建一个，唯一键索引能创建多个

#唯一键索引速度最快，能创建唯一键索引就创建唯一键索引

#不经常用的字段，不要创建索引

#删除索引的时候按照索引的名字删除

#索引的名字不用指定，索引名=字段名 == 索引名_n

#添加主键索引，唯一键索引，普通索引

$$: primary key unique keyindex

alter table xx add $$ suo_name(name);

查看索引

#方式一：

show index from city;

#方式二：

mysql> desc city;

+-----+

| Key |

+-----+

| PRI |#主键索引

| MUL |#普通索引

| UNI |#唯一键索引

| MUL |

+-----+

mysql> desc test3;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| age | varchar(6) | YES | UNI | NULL | |

| a4 | int(11) | NO | UNI | 0 | |

| a5 | int(11) | YES | MUL | NULL | |

| a6 | int(11) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

#方式三

show create table xx;

mysql> show index from test3;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| test3 | 0 | 1_key | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

| test3 | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | |

#使用多种方式查看表内主键，可以查看索引的种类(desc)，索引名(Key_name)

#和索引字段(Column_name)，前缀索引的顺序，索引的类型

#通过分析字段名和索引名可以查看一个字段是否有多个索引(与索引优化有关)

删除索引

#删除主键索引

mysql> alter table test3 drop primary key;

#删除唯一键索引，普通索引

mysql> alter table city drop index index_name;

索引根据配置文件分类

1.前缀索引

2.联合索引

注意

1.创建索引的时候会对数据进行重新排序('耗时',排序规则多样)

2.创建索引的时候会'占用磁盘空间'，所以索引不是越多越好

3.在'同一列'上避免创建多种索引

4.避免在数据'很长的字段上创建索引'，如果要创建就创建前缀索引,增加创建索引的时候的排序速度

前缀索引(列)

#添加前缀索引为4(前缀索引并不是越小越好)

太小的索引值会是数据查找的精确度降低

无论字段有没有其他索引，都可以添加前缀索引

#添加主键和前缀索引

mysql> alter table test3 add primary key p_id(name(4));

#添加唯一键和前缀索引

mysql> alter table test3 add unique key prrri_id(name(4));

#添加普通索引和前缀索引

mysql> alter table test3 add index suo_name(name(4));

联合索引(复合索引)(字段)

#命名规则：表名_字段名

1、需要加索引的字段，在'where条件中使用'

2、'数据量少'的字段不需要加索引

3、如果where条件中是'OR关系'，加索引不起作用

4、符合最左原则('有特例',解决办法是将索引字段的顺序调换一下)

create table xq(id int primary key,name varchar(4),gender enum('m','f'),age int);

insert xq values(1,'xx','m','18');

insert xq values(2,'xx','m','18');

insert xq values(3,'xx','m','18');

insert xq values(4,'xx','m','18');

show index from xq;

+-------+------------+----------+--------------+

| Table | Non_unique | Key_name | Seq_in_index

+-------+------------+----------+--------------+

| xq | 0 | PRIMARY | 1

#创建联合索引(联合索引有联合顺序)(最常用的字段创建联合索引)

alter table xq add index ls_key(id,name,gender);

show index from xq;

| xq | 0 | PRIMARY | 1 id

| xq | 1 | ls_key | 1 id

| xq | 1 | ls_key | 2 name

| xq | 1 | ls_key | 3 gender

#联合索引3种情况

1.全部走索引(1开头)

2.部分走索引(1开头)

3.不走索引 (不以1开头)

explain

#作用

explain显示了MySQL如何使用'索引'来处理select语句以及'连接表'。可以帮助选择更好的索引和写出更优化的查询语句。

#语法

explain + select语句

mysql> explain select * from test2;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

mysql> explain select * from test2\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test2#表

type: index#最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

possible_keys: NULL#表中的索引

key: uni_key#实际使用的索引，强制(use index index_name),忽略(ignore index index_name)

key_len: 5#使用的索引的长度(越小越好)

ref: NULL#显示索引的哪一列被使用了，如果可能的话，是一个常数

rows: 1#必须检查的用来返回请求数据的行数

Extra: Using index #查询的次数，当查询的数据量较大的时候，并不准确

extra:

using temporary 使用了group by出现这个

using filesort使用了order by出现这个

using join buffer 使用了join on出现这个

#查询中国和美国的数据

mysql> select * from city where countrycode='CHN' or countrycode='USA';

mysql> select * from city where countrycode in ('CHN','USA');

mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

速度： union all > in>or

#使用explain查看

mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

+----+--------------+------------+------+---------------+-------------+-----

#id与执行顺序有关

#select_type 索引类型

#type 是explain的类型

#key_len 是前缀索引的长度

#rows 查询的次数，当查询的数据量较大的时候，并不准确(合并重复项)

扩展group by

create database fang;

use fang;

#建表

mysql> create table jixiao(id int,name varchar(20) charset utf8,jixiao int,product varchar(10) charset utf8);

show create table fz;

show tables;

desc fz;

#插入数据

mysql> insert jixiao values(1,'qiudao','1000','房地产'),(2,'niulei','10','房地产'),(3,'lijianpeng','100','汽车'),(4,'qiandao','200','汽车');

select count(*) from fz;

#查询不同行业绩效最高的人

mysql> select name,sum(jixiao),product from jixiao group by product;

+------------+-------------+-----------+

| name | sum(jixiao) | product |

+------------+-------------+-----------+

| qiudao | 1010000 | 房地产 |

| lijianpeng | 300000 | 汽车 |

+------------+-------------+-----------+

#查询房地产行业绩效最高的人

mysql> select name,sum(jixiao),product from jixiao group by product having product='房地产';

+--------+-------------+-----------+

| name | sum(jixiao) | product |

+--------+-------------+-----------+

| qiudao | 1010000 | 房地产 |

+--------+-------------+-----------+

select name,sum(jixiao),product from jixioa group by product;

select name,sum(jixiao),product from jixioa group by product having 字段='值';

#1.对group by后面的字段去重

#1.sum() 根据group by的值相加

#having条件语句

select 字段1,sum(字段2),字段3... from 表1 ... group by 字段x having 字段='值';

查询数据的级别

1.全表扫描(type类型是all)，不走索引，查询速度最慢

#查看结果来判断查询级别

mysql> explain select * from country;

+----+-------------+---------+------+---------------+------+---------+------

+----+-------------+---------+------+---------------+------+---------+------

| 1 | SIMPLE | country | ALL |

#查看字段是否有索引

mysql> desc city;

+-------------+----------+------+-----+---------+----------------+

+-------------+----------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| CountryCode | char(3) | NO | MUL | | |

| Population | int(11) | NO | | 0 | |

+-------------+----------+------+-----+---------+----------------+

查看结果来判断查询级别

mysql> explain select District from city;

+----+-------------+-------+------+

+----+-------------+-------+------+

| 1 | SIMPLE | city | ALL |

#全表扫描的情况

一：查询数据库所有数据的时候(select *)

二：有索引，但是没有走索引

没有设置索引 (desc xx;)

索引损坏

explain select name from country;

desc

---------------------------------------------------------------

2.索引扫描'级别'

一：index慢，全索引扫描(!=)

explain select * from city;

explain select * from city where countrycode !='CHN'

二：range范围查询(or >

explain select * from city where countrycode='CHN'or countrycode='USA';

explain select * from city where countrycode='CHN'or countrycode='USA' or countrycode='BRA' limit 100;

三：ref精确查询(=)

explain select * from city where countrycode='CHN'

四：eq_ref使用join on

五：const查询条件是唯一索引或者主键索引，#列

explain select * from city where id=1;

六：system快，查询级别与const一样，当数据很少的时候为该级别，#列

七：null使用索引查询表的数量的时候，#列

desc city;

alter table city add index aa(population);

explain select max(population) from city;

explain select min(population) from city;

索引的建立

#索引的建立原则

1.能创建唯一索引就创建唯一索引

2.为经常需要排序，分组，联合的字段建立索引

3.为经常查询的字段建立索引

4.尽量使用前缀索引

5.限制索引的数目，删除很少使用的索引

不走索引的情况

1.'没有查询条件'，或者'查询条件没有索引'

2.查询的结果占总数据的'20%左右以上'

3.'要查询的数据'就是表中的大部分数据

4.索引损坏(反复建立删除)

5.查询条件带了特殊符号(+ —)

#'='左侧有特殊符号

mysql> select * from city where id-1=1;

+----+----------+-------------+----------+------------+

+----+----------+-------------+----------+------------+

| 2 | Qandahar | AFG | Qandahar | 237500 |

+----+----------+-------------+----------+------------+

mysql> explain select * from city where id-1=1;

| 1 | SIMPLE | city | ALL | #

#'='右侧有特殊符号

mysql> select * from city where id=3-1;

+----+----------+-------------+----------+------------+

+----+----------+-------------+----------+------------+

| 2 | Qandahar | AFG | Qandahar | 237500 |

+----+----------+-------------+----------+------------+

mysql> explain select * from city where id=3-1;

6.隐式转换(字段类型设置与插入类型不同，导致不走索引)

#建表

mysql> create table cs(id int,name varchar(10));

#插入数据

mysql> insert cs values(1,'zz'),(2,007);

#建立索引

mysql> alter table cs add unique key uu_key(id);

mysql> alter table cs add unique key uuu_key(name);

mysql> desc cs;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | UNI | NULL | |

+-------+-------------+------+-----+

mysql> explain select * from cs where name='007';

mysql> explain select * from cs where name=007;

7.%在最前面不走索引(like %x)

8.联合索引查询不按顺序

#int类型不加引号

林语堂表弟

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql索引有字符集_mysql数据库字符集和索引

#什么是字符集#字符集：是一个计算机支持的所有抽象字符的集合。#字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。编码：把人类可以识别的信息转化为机算计认识的0和1解码：把机算计存储的0和1转化为人类可以识别的信息乱码：编码解码的过程，导致一些数据不能解析ASCII：美国gbk 中国,一个汉字占用2个字节，#范围大于utf8gbk2312#范围大于gbkutf8一个汉字...
复制链接

扫一扫