mysql使用

最新推荐文章于 2022-02-19 11:55:52 发布

qq_32754539

最新推荐文章于 2022-02-19 11:55:52 发布

阅读量205

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_32754539/article/details/100033157

版权

文章目录

读深入浅出mysql数据库、优化与维护笔记

mysql下载:https://www.mysql.com/downloads/
mysql在线手册:https://dev.mysql.com/doc/
mysqlbug列表:https://bugs.mysql.com/
mysql最新消息:https://www.mysql.com/news-and-events/newsletter/

DDL：数据定义语言，定义不同数据段，数据库，表，列，索引等数据库对象，常用语句关键字create，drop，alter等。
DML:数据操纵语句，用于添加，修改，删除，更新和查询数据库记录，并检查数据完整性，常用包括insert，delete，update和select等。
DCL:数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要语句包括关键字grant、revoke等。

DDL语句(数据库和表操作)

数据库操作

创建数据库
create database test1;
删除数据库
drop database test1;
查看所有数据库
show databases;
使用数据库
use databases;

表操作

use databases之后才可以操作tables
查找数据库中的表
show tables;
创建表
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
查看表
desc emp;
查看创建表命令
show create table emp \G;
删除表
drop table emp;

修改表字段
alter table emp modify ename varchar(20)
添加表字段
alter table emp add column age int(3);
删除表字段
alter table emp drop column age;
修改字段名
alter table emp change age age1 int(10);
修改字段排序顺序
alter table emp add birth date after ename;
修改将字段放到最前面
alter table emp modify age int(3) first;
更改表名
alter table emp rename emp1;

DML语句(表数据操作)

1、插入记录
insert into emp(ename,hiredate,sal,deptno) values('zzxl','2000-01-01','2000',1);
或者不用字段名称
insert into emp values('lisa','2003-02-01','3000',2);

一次性插入多条
insert into dept values(5,'dept'),(6,'dept');

2、更新记录
update emp set sal=4000 where ename='lisa';

mysql中同时更新多个表的数据
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname = a.ename where a.deptno=b.deptno;

多表语法更多用在根据一个表的字段动态的更新另一个表的字段

3、删除记录
删除记录
delete from emp where ename='dony';

同时删除多表数据
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

注意不管单表还是多表，不加where会删除全部记录

4、查询记录
select * from emp;
select ename,hiredate from emp;

查询不重复的记录
select distinct deptno from emp;

条件查询
select * from emp where deptno = 1;

多条件查询
select * from emp where deptno = 1 and sal<3000;

排序和条数限制
select * from emp order by deptno;
select * from emp order by deptno,sal desc;

order by 默认升序排列即asc,当为desc的时候为降序；

限制条数(limit)
select * from emp order by sal limit 3;
限制第一条到第三条
select * from emp order by sal limit 1,3;

聚合(统计公司或部门人数)
select count(1) from emp;
统计各个部门人数
select deptno,count(1) from emp group by deptno;
即统计部门人数，又统计总人数
select deptno,count(1) from emp group by deptno with rollup;
统计人数大于一的部门
select deptno,count(1) from emp group by deptno having count(1) > 1;
统计公司员工薪水
select sum(sal),max(sal),min(sal) from emp;

表连接
内连接
select ename,deptname from emp,dept where emp.deptno=dept.deptno;

外连接
分成左连接和右连接
左连接：包含所有左边表记录甚至是右边表没有的记录；
右连接：包含所有右边表的记录甚至是左边表所没有的记录；

查询emp中所有用户名和所在部门名称
select ename,deptname from emp left join dept on emp.deptno = dept.deptno;
或者
select ename,deptname from dept right join emp on emp.deptno = dept.deptno;

子查询

需要另一个条件的结果
关键字有in、not in、=、!=、exists、not exists等
select * from emp where deptno in(select deptno from dept);

注意：mysql4.1以前版本不支持子查询，需要用表连接实现功能。
表连接很多情况下用于优化子查询

记录联合(纵向联合非横向)

UNION和UNION ALL
区别 UNION将UNION ALL查询数据进行distinct去重复
select deptno from emp
union all
select deptno from dept;

去重复
select deptno from emp
union
select deptno from dept;

DCL语句(权限操作)

管理用户权限

创建一个数据用户zl，具有对sakila数据库中所有表的SELECT/INSERT权限
grant select,insert on sakila.* to 'zl@localhost' identified by '123';
exit;

收回insert权限
revoke insert on sakila.* from 'zl@localhost';
exit;

mysql数据库类型

分成数值类型，字符串类型、日期和时间类型
数值类型

用alter table t1 modify id1 int zerofill;
可以让0填满整个int类型
比如1，使用zerofill后为，00001

但是使用zerofill后会为字段自动添加UNSIGNED属性
UNSIGNED属性的作用：比如int类型时-127-126，范围会编程0-255。

数值自增长创建语句
CREATE TABLE AI(
	ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	NAME VARCHAR(100) DEFAULT NULL);

float double decimal的使用

float double是浮点数，decimal定点数(适合精确度较高比如货币)
都不可以使用如float(M,D) M整数位，D小数位。如123.22 中  M=123  D=2
不指定精度
float 和 double由操作系统决定
decimal默认是10

注意指定精度为非标准用法，最好不要用于数据库迁移

bit用于存储二进制字段

创建
CREATE TABLE t2(
	id2 BIT(2) DEFAULT NULL;
)

注意:BIT(1~64位)

二进制格式查询使用
select bin(id),hax(id) from t2;

因为插入使用的是二进制，所以需要足够长度

日期类型
日期格式

CREATE TABLE t(d date,t time,dt datetime);
INSERT INTO t VALUES(now(),now(),now());
SELECT * FROM t;

在这里插入图片描述

TIMESTRAMP特性

范围比datetime较小(1970-01-01 08:00:01 - 2038某一天)
每个表只能有一列会有CURRENT_TIMESTRAMP
插入时间会根据时区变化而变化

YEAR类型用于记录年份

比使用date更节省空间

采用不同格式插入日期

CREATE TABLE t6(dt datetime);
INSERT INTO t6 VALUES('2007-9-3 12:10:10');
INSERT INTO t6 VALUES('2007/9/3 12+10+10');
INSERT INTO t6 VALUES('20070903121010');
INSERT INTO t6 VALUES(20070903121010);
SELECT * FROM t6;

字符串类型
在这里插入图片描述
char和varchar区别

长度上面差距，varchar会保留字符串末尾的空格，char会删除末尾的空格

binary和varbinary类型

这两个类型包含二进制字符串
CREATE TABLE t(C BINARY(3));
INSERT INTO t SET c='a';
SELECT * ,hex(c),c='a',c='a\0',c='a\0\0' from t;

在这里插入图片描述

enum枚举类型(1~65535个成员变量)
1~255一个字节存储
255~65535两个字节存储

CREATE TABLE t(gender enum('M','F'));
INSERT INTO t VALUES('M');
INSERT INTO t VALUES('1');
INSERT INTO t VALUES('f');
INSERT INTO t VALUES('2');
INSERT INTO t VALUES(NULL);
SELECT * FROM t;

特性，不区分大小写，只能取其中一个。

在这里插入图片描述

SET类型(1~64)和enum相似
可以存储多个集合

CREATE TABLE t(
	c SET ('a','b','c','d')
);
INSERT INTO t VALUES('a,b');
INSERT INTO t VALUES('a,b,a');
INSERT INTO t VALUES('a,d');
INSERT INTO t VALUES('a,c'),('a');
SELECT * FROM t;

在这里插入图片描述

MYsql运算符

算术运算符
普通加减乘除和取模

select 0.1+0.3333,0.1-0.3333,0.1*0.3333,1/2,1%2;
select 1/0,1%0;
select 3%2,mod(3,2);

比较运算符
用于比较数字，字符和表达式
在这里插入图片描述

SELECT 1=0,1=1,NULL=NULL;
结果:0 , 1 , null
SELECT 1<>0,1<>1,NULL<>NULL;
结果:1 , 0 , null
SELECT 1<=>1,2<=>0,0<=>0,NULL<=>NULL;
结果:1 , 0 , 1
SELECT 'a'<'b','a'<'a','a'<'c',1<2;
结果:1 , 0 , 1
SELECT 'bdf'<='b','b'<='b',0<1;
结果:0 , 1 , 1
SELECT 'a'>'b','abc'>'a',1>0;
结果:0 , 1 , 1
SELECT 'a'>='b','abc'>='a',1>=0;
结果:0 , 1 , 1
SELECT 10 BETWEEN 10 AND 20,9 BETWEEN 10 AND 20;
结果:1 , 0 
SELECT 1 IN (1,2,3) , 't' IN ('t','a','b','1','e'),0 in (1,2);
结果:1 , 1 , 0
SELECT 0 IS NULL,NULL IS NULL;
结果:0,1
SELECT 0 IS NOT NULL,NULL IS NOT NULL;
结果:1 , 0
SELECT 123456 LIKE '123%',123456 LIKE '%123%', 123456 LIKE '%321%';
结果:1 , 1 , 0
SELECT 'abcdef' REGEXP 'ab','abcdefg' REGEXP 'k';
结果:1 , 0

逻辑运算符
与，或，非，异或

SELECT (1 AND 1) , (0 AND 1) , (3 AND 1) , (1 AND NULL);
非空和非0为1
结果：1,0,1,NULL
SELECT (1 OR 0), (0 OR 0) , (1 OR NULL),(1 OR 1),(NULL OR NULL);
一个不为0为1，
结果：1,0,1,1,null
SELECT NOT 0,NOT 1,NOT NULL;
结果数为0 返回1
结果:1,0,null
SELECT 1 XOR 1,0 XOR 0, 1 XOR 0 , 0 XOR 1 , NULL XOR 1;
相同为0，不同为1，有NULL为空
结果:0，0，1，1,null

位运算符

在这里插入图片描述

select 2&3; 10 & 11 = 10
2
select 2|3; 10 | 11 = 11
3
select 2^3;  10 ^ 11 = 01
1
select ~1; 18446744073709551614
二进制63个1和0；
select 100>>3; 12
0001100100  0000001100
select 100<<3; 800
0001100100 1100100000

运算符的优先级
在这里插入图片描述

常用函数

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	连接S1到Sn为字符串
INSERT(str,x,y,instr)	将字符串str从第x个位置开始，y个字符长字串替换成instr字串
LOWER(str)	将str所有字符变成小写
UPPER(str)	将str所有字符变成大写
LEFT(str,x)	返回str从左边开始中的第x个字符
RIGHT(str,x)	返回str从右边开始第x个字符
LOWER(str)	将str所有字符变成小写
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到n个字符长度为止
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到n个字符长度为止
LTRIM(str)	去除字符串左边的空格
RTRIM(str)	去除字符串右边的空格
REPEAT(str,x)	返回重复x次的结果
REPLACE(str,a,b)	用字符串b替换str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉头尾空格
SUBSTRING(str,a,b)	返回从a位置到b位置的字符串

INSERT(str,x,y,instr)
select INSERT('beijing2008you',12,3,'me');
结果 beijing2008me

 LEFT(str,x)
 select LEFT('beijing2008',7);
 结果:beijing
LPAD(str,n,pad) 
SELECT LPAD('2008',20,'beijing');
结果:beijingbeijingbe2008

 REPEAT(str,x) 
 SELECT REPEAT('mysql',3);
 结果:mysql mysql mysql

STRCMP(s1,s2)
比较ASCII编码大小
SELECT STRCMP('a','b'),STRCMP('b','b'),STRCMP('c','b');
-1 0 1

数值函数

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数
FLOOR(x)	返回小于x的最小整数
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	返回参数x的四舍五入保留y位小数
TRUNCATE(x,y)	返回数字x截断为y位的小数结果

SELECT ROUND(1.235,2),TRUNCATE(1.235,2)
结果：1.24 1.23

日期和时间函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前时间和日期
UNIX_TIMESTAMP(date)	返回日期date的UNIX的当前时间戳
FROM_UNIXTIME()	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的次数

SELECT UNIX_TIMESTAMP(now());
1567436137
SELECT FROM_UNIXTIME(1567436073);
2019-09-02 22:54:33

SELECT DATE_FORMAT(now(),'%M,%D,%Y');
September,2nd,2019

SELECT DATEDIFF('2008-08-08',NOW());
-4042

SELECT NOW() current,DATE_ADD(now(),INTERVAL 31 DAY) after31days,DATE_ADD(now(),INTERVAL '1_2' YEAR_MONTH) after_oneyear_twomonth;
结果:

在这里插入图片描述

fmt格式(DATE_FORMAT使用)
在这里插入图片描述

日期间隔类型(DATE_ADD函数使用)
在这里插入图片描述

流程函数

函数	功能
IF(value,t,f)	如果value是真，返回t;否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1,否则返回value2
CASE WHEN [value1] THEN[result1] … ELSE [default] END	如果value1是真返回result1,否则返回default
CASE [expr] WHEN [value1] THEN[result1] … ELSE [default] END	如果expr等于value1返回result1,否则返回default

CREATE TABLE salary (userid int,salary decimal(9,2));
INSERT INTO salary VALUES(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
SELECT IF(salary>2000,'high','low') FROM salary;
显示high和low
SELECT IFNULL(salary,0) FROM salary;
替换null为0
SELECT CASE WHEN salary <= 2000 THEN 'low' ELSE 'high' END FROM salary;
显示high和low
SELECT CASE WHEN salary <= 2000 THEN 'low' WHEN salary >2000 AND salary <=4000 THEN 'mid' WHEN salary > 5000 THEN 'high' END FROM salary;

其他常用函数

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户
INET_ATON(ip)	返回ip地址的数字表示
INET_NTOA(num)	返回数字代表的ip地址
PASSWORD(str)	返回字符串str的加密版本
MD5(str)	返回字符串str的MD5加密

SELECT INET_ATON('192.168.0.1');
3232235521
SELECT INET_NTOA(3232235521);
192.168.0.1
这样可以更方便进行ip或者网段比较

CREATE TABLE t1(
	ip varchar(20) DEFAULT NULL
);
INSERT INTO t1 VALUES('192.168.1.1'),('192.168.1.3'),('192.168.1.6'),('192.168.1.10'),('192.168.1.20'),('192.168.1.30');
SELECT * FROM t WHERE INET_ATON(ip)>=INET_ATON('192.168.1.3') AND INET_ATON(ip) <= INET_ATON('192.168.1.20');
SELECT MD5('123456');
SELECT PASSWORD9('123456');

比较网段结果
在这里插入图片描述

表类型（存储引擎的选择）

创建表的时候创建存储引擎
5.5前默认MYisam,之后默认InnoDB

show variables like 'table_type';

CREATE TABLE ai (
	i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(i)
) ENGINE=MyISAM DEFAULT CHARSET = gbk;

CREATE TABLE country(
	contry_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
contry VARCHAR(50) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (contry_id)
)ENGINE=InnoDB DEFAULT CHARSET=gbk;

ALTER TABLE ai ENGINE = InnoDB;

SHOW CREATE TABLE ai \G

存储引擎区别
在这里插入图片描述

MyISAM引擎
不支持事务和外键，优势访问速度快，对事务完整性没有要求，或者以SELECT、INSERT语句为主
MyISAM引擎分为三个文件
文件名.frm(存储表定义)
文件名.MYD(MYData,存储数据)
文件名.MYI(MYIndex,存储索引)
创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，也就是说不同MYISAM的索引文件和数据文件放置位置不同，文件路径是绝对路径并且具有访问权限。
MYSIAM表文件可能损坏，用CHECK TABLE语句检查健康，并且用REPAIR TABLE 语句修复一个MYSIAM表的健康。

MYSIAM分为三种存储方式，静态表，动态表，压缩表。
静态表，非变长字符串，存储迅速，容易缓存，出现故障容易修复，缺点占用空间比动态表多，静态表多余的会按照空格补齐，但是获取会去掉后面对的空格。

CREATE TABLE Myisam_char(name char(10)) ENGINE = MYISAM;
INSERT INTO Myisam_char VALUES('abcde'),('abcde  '),('  abcde'),('  abcde  ');
SELECT name,LENGTH(name) FROM Myisam_char;

动态表
变长字符串，记录不是固定长度，存储优点占用空间较少，但是频繁更新和删除会产生碎片，需要定期执行OPTIMIZE TABLE 语句或mysiamchk -r 命令改善性能，出现故障时回复比较难。

压缩表
myisampack工具创建，占用非常小的磁盘空间，因为每个记录都是单独压缩的。所以只有非常小的访问开支。

InnoDB引擎
具有提交、回滚、崩溃恢复的事务安全。但是对比MYISAM，InnoDB写的效率会更低，并且会占用更多磁盘空间保留数据。
不同于其他引擎的地方。
1、自动增长列
当插入值为0或者空的时候，实际插入自动增长后的值，
SELECT LAST_INSERT_ID();
获取最后一此插入的自增长值

CREATE TABLE autoincre_demo(
	i smallint not null auto_increment,
	name varchar(10) , primary key(i)
)engine=innodb;
insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
select * from autoincre_demo;
insert into autoincre_demo values(4,'4');
select LAST_INSERT_ID();
结果：2
INSERT INTO autoincre_demo(name) values('5'),('6'),('7');
SELECT LAST_INSERT_ID();
结果：5

InnoDB自动增长列必须是索引，如果组合索引，必须是组合索引的第一列。
MYSIAM组合索引可以是其他列，这样插入时按照组合索引前面几列进行排序递增的。

外键约束
MYSQL支持外键约束的表只有InnoDB，创建外键的时候，附表必须有对应的索引，子表创建外键的时候也会创建索引。

DROP TABLE country;
CREATE TABLE country(
	country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	country VARCHAR(50) NOT NULL,
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE city;
CREATE TABLE city(
	city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	city VARCHAR(50) NOT NULL,
	country_id SMALLINT UNSIGNED NOT NULL,
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(city_id),
	KEY idx_fk_country_id(country_id),
	FOREIGN KEY(country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建外键约束指定ON DELETE RESTRICT ON UPDATE CASCADE表示在删除的时候子表有对应记录就不删除，修改的时候，对应子表记录也修改。

当某个表被其他表外键关联的时候，主键或对应索引禁止删除
导入多个表的时候，如果忽略表的导入顺序，可以通过暂时关闭索引，处理，关闭命令时，SET FOREIGE_KEY_CHECKS=0 执行完 SET FOREIGE_KEY_CHECKS=1 回复

3、存储方式
使用共享表空间存储：这种方式存储的表结果保存在.frm中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
使用多表空间存储：
在这里插入图片描述

在这里插入图片描述
单表备份回复目标到数据库需要借助mysqldump和mysqlimport

MEMORY存储引擎
使用存在于内存的内容创建表。每个memory实际对应一个磁盘文件，格式.frm。MEMORY引擎访问速度非常快，因为默认放在内存中，而且默认HASH索引，但是一旦关闭服务，数据就会丢失。

CREATE TABLE tab_memory ENGINE=MEMORY
SELECT city_id,city,country_id
FROM city GROUP BY city_id;
SELECT count(*) from tab_memory;

create index mem_hash USING HASH on tab_memory(city_id);

在这里插入图片描述

MERGE存储引擎
适用于对多个表进行查询和更新操作，缺点不能只能插入对应表格。

不能使用分区表时候使用

DROP TABLE IF EXISTS payment_2006;
CREATE TABLE payment_2006(
	country_id smallint,
	payment_date datetime,
	amount DECIMAL(15,2),
	KEY idx_fk_country_id(country_id)
)ENGINE=MYISAM;

DROP TABLE IF EXISTS payment_2007;
CREATE TABLE payment_2007(
		country_id smallint,
	payment_date datetime,
	amount DECIMAL(15,2),
	KEY idx_fk_country_id(country_id)
)ENGINE=MYISAM;


DROP TABLE IF EXISTS payment_all;
CREATE TABLE payment_all(
		country_id smallint,
	payment_date datetime,
	amount DECIMAL(15,2),
	KEY idx_fk_country_id(country_id)
)ENGINE=MERGE union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);
insert into payment_2007 values(1,'2007-05-01',350000),(2,'2007-07-15',220000);

select * from payment_2006;
select * from payment_2007;
select * from payment_all;

TokuDB
在这里插入图片描述

在这里插入图片描述

如何选择合适的存储引擎
MYISAM:默认的MYSQL插件式存储引擎，如果应用以读操作和插入操作为主，只有很少用到更新和删除的操作，对事物完整性和并发性要求不是很高，那么选择这个存储引擎非常合适，MYISAM实在WEB、数据仓库和其他应用环境最常用的存储引擎之一。
InnoDB:用于事务处理应用程序、支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了操作插入和查询以外，还包括很多更新和删除操作。inndb比较合适。对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnDB都合适
MEMORY:将数据保存在RAM中，需要快速定位和其他类似数据的环境下，可以提供极快的访问速度。MEMORY的缺陷是对表的大小限制，太大的表无法缓存在内存当中，数据库异常数据可以恢复。
MERGE:用于将一系列MYSIAM表按照逻辑的方式组合在一起，并作为引用对象，优点在于可以突破对单个MYISAM表达大小的限制，并且通过将不同的表分不到多个磁盘上，有效改善MERGE访问效率。

选择合适的数据类型

char和varchar存储选择
在这里插入图片描述
注意varchar(4)存5个字节在非严格模式下面会发生，但是严格模式下面则超出长度的字节不会存储
对于变化不大并且查询速度有要求的用char
不同引擎使用
MYISAM:建议使用char代替可变长度的数据列
MEMORY存储引擎：目前使用固定长度的数据行存储，无论char或varchar都没有关系。
InnoDB存储引擎：建议使用varchar，因为内部存储长度没有区分固定长度和变长长度；

TEXT和BLOB
一般保存少量文本的时候使用char或varchar，保存大量文本的时候使用TEXT和BLOB
BLOB能够保存二进制格式比如招聘，TEXT只能保存字符数据文章或日志
因为BLOB和TEXT会发生性能问题，删除的时候会留下"空洞"
提高性能建议定期使用OPTIME TABLE功能对类表进行碎片整理

消除text的空洞空间

drop table if EXISTS t;
CREATE TABLE t(id varchar(100),context text);
insert into t values(1,repeat('haha',100));
insert into t values(2,repeat('haha',100));
insert into t values(3,repeat('haha',100));
insert into t select * from t;
insert into t select * from t;

exit;

du -sh t.*;

OPTIMIZE TABLE t

du -sh t.*;

创建散列索引加快text和blob等大文本的查询
合成索引就是根据大文本字段的内容简历索引值，并把这个值单独存在索引列。这种技术只能用于精确匹配，可以使用MD5()，SHAI()，CRC32()或者自己的应用程序计算散列值。
精确查询

drop table if EXISTS t;
create table t(id varchar(100),context blob,hash_value varchar(40));
insert into t values(1,repeat('beijing',2),md5(context));
insert into t values(2,repeat('beijing',2),md5(context));
insert into t values(3,repeat('beijing 2008',2),md5(context));
select * from t;
select * from t where hash_value=md5(repeat('beijing 2008',2));

模糊查询
为字段的前n列创建索引

create index idx_blob on t(context(100));
select * from t where context like 'beijing%';

不必要避免检索text和blob数据
把blob或text分离到其他表当中，既可以避免select * 查询所有，把原来数据列改成固定数据。又可以减少主表中的碎片。

浮点数与定点数

浮点数和定点数区别
插入大于精度的时候，浮点数插入会进行四舍五入。
定点数在sqlmode默认情况下，会报错并且会插入，如果sqlmode在TRADITIONAL（传统模式），系统会直接报错。

create table test(c1 float(10,2),c2 decimal(10,2));
insert into test values(131072.32,131072.32);
select * from test;

在这里插入图片描述
从这里可以看出，浮点型数据会产生误差，而定点数不会产生误差。

注意：
关于浮点数和定点数的应用。
1、浮点数存在误差问题。
2、对货币等对精度敏感的数据使用定点数，。
3、编程中注意浮点数误差，并且尽量避免进行比较
4、注意浮点数中一些特殊值的处理。

日期类型选择
date time datetime timestramp
1、根据满足应用最小存储日期类型，如果应用只需要年份，那么用1字节存储year,不需要四字节date,
2、如果记录时分秒并且年代久远使用datetime,不适用timestramp。
3、如果记录不同时区使用，最好使用timestramp，和实际时区对应。

对于字符类型根据存储引擎选择
对于精度要求较高，用decimal,
对含有text和blob的应用，如果经常删除和修改记录要定时执行optimize table进行碎片整理
日期类型根据实际需要选择满足应用的最小存储的日期类型。

字符集

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

如何选择合适的字符集
1、应用要处理各种各样的文字，或者将发布到使用不同国家或地区，使用Unicode字符集，mysql目前是utf-8
2、如果数据库涉及已有数据的导入，就充分考虑已有数据的兼容性，假如已有数据是GBK文字，选择GB2312-80为数据库字符集很可能出现文字无法正确导入。
3、如果数据库支持一般中文，且数据量很大，性能要求很高，选择双字节定长编码的中文符集，比如GBK。
而UTF-8汉字编码需要3个字节。
4、如果数据库需要大量字符运算使用定长字符集，因为定长字符集处理速度比边长字符集要快
5、如果所有客户端都支持相同字符集，则优先算则该字符集作为数据库字符集。

Mysql字符集和校对规则，4个级别的默认设置：服务器级、数据库级、表级和字段级。

qq_32754539

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql使用

mysql下载:https://www.mysql.com/downloads/mysql在线手册:https://dev.mysql.com/doc/mysqlbug列表:https://bugs.mysql.com/mysql最新消息:https://www.mysql.com/news-and-events/newsletter/DDL：数据定义语言，定义不同数据段，数据库，表，列，...
复制链接

扫一扫