mysql常用数据类型 数据类型转换 数据加解密 数据高级操作 内存表和临时表

一、mysql整数类型

1、实际中所有的数据类型都可以指定显示长度，比如int(10)；

2、可以指定无符号属性unsigned，比如int unsigned；

3、可以指定属性zerofill，int(4) zerofill，比如5会返回0005；

4、截断取决于数据类型范围，tinyint(-128，127)，当值小于-128时值按-128存储，当值大于127时值按127存储；

5、数据类型长度：TINYINT=1字节、SMALLINT=2字节、MEDIUMINT=3字节、INT,INTEGER=4字节、BIGINT=8字节；

二、mysql浮点数类型

近似浮点数：

1、近似浮点数：float=4字节、double=8字节；

2、指定范围type(M,D)，M是数据digit的总个数，D是小数digit的总个数，精度决定数据存储范围；

精确浮点数：

1、精确浮点数常用于金钱，包括DECIMAL和NUMERIC，并且他们等价；

2、指定范围type(M,D)，M是数据digit的总个数，D是小数digit的总个数，精度决定数据存储范围；

三、mysql字符串类型

1、char(max count)，最大长度255，char(16)存储大小是16字节；

2、varchar(max count)，最大长度65535，varchar(16)存储大小是实际字符串长度加一个固定值；

3、binary和varbinary类似char和varchar，binary存储字节，char存储字符；

4、text用于存储大量文本，TINYTEXT<=256字节、TEXT<=64K、MEDIUMTEXT<=16M、LONGTEXT<=4G；

5、超过字段申明长度的字符串会被截断；

6、字符串使用单引号或双引号都可以，转义字符使用反斜杠，单引号中可以用''表示单引号，双引号中可以用""表示双引号；

7、连续的字符串会连接成一个字符串，不同字符串之间允许空白，或者使用concat进行字符串连接；

8、0x开头的十六进制数据是字符串类型，语句里面包含保留字需要使用反引号；

四、mysql时间类型

1、date，'YYYY-MM-DD'，取值范围'1000-01-01' to '9999-12-31'

2、datetime，'YYYY-MM-DD HH:MM:SS'，取值范围'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

3、timestamp，'YYYY-MM-DD HH:MM:SS'，取值范围'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

4、timestamp取值范围较小，存储时会转换为UTC时间，支持自动更新，所以不建议使用；

5、curdate函数返回日期，curtime函数返回时间，now函数返回日期和时间；

6、timestampdiff以指定单位计算时间差，忽略结果的小数部分；

7、datediff以天为单位计算时间差，只比较日期，不比较时间；

五、mysql数据类型转换

1、操作符或函数参数不兼容，隐式类型转换发生，参数自动转换成需要类型；

2、显示类型转换函数有binary、cast、convert；

3、数字转换成字符串：cast(8.8 as char)、convert(8.8,char)、concat(8.8)；

4、比较操作符2个参数都是整数则按整数比较，2个参数都是字符串则按字符串比较；

5、比较操作符1个参数是整数，1个参数是字符串，则按整数比较；

6、比较操作符只要有1个参数是NULL，结果就是NULL；

7、类型转换说明：http://www.3scard.com/data/download/mysql55/functions.html#type-conversion

8、类型转换函数：http://www.3scard.com/data/download/mysql55/functions.html#function_cast

六、mysql数据加解密

1、HEX(str)、HEX(N)，字符串或整数转十六进制字符串；

2、UNHEX(str)，十六进制字符串转原始二进制字符串；

3、MD5(str)，哈希算法返回32个十六进制字符；

4、SHA(str)、SHA1(str)，哈希算法返回40个十六进制字符；

5、AES_ENCRYPT(str,keystr)，128比特密钥加密数据，keystr=UNHEX('keyhex') or MD5('passphrase')；

6、DES_ENCRYPT(str[,{keynum|keystr}])，128或192比特密钥加密数据，密钥参数是keystr则参照上面AES；

七、mysql数据高级操作

1、预编译的存储过程可以提高执行效率，申明语法是create procedure或create function；

2、procedure一般调用sql语句，function一般不调用sql语句，且两者调用方式不大一样；

3、UDF自定义函数的申明语法是create function，自定义函数使用动态链接库，因此效率非常高；

4、触发器用于在SQL执行前或执行后进行一些操作，触发器的调用基于数据行，因此非常消耗资源；

八、mysql存储过程

1、mysql5.0版本开始支持存储过程，存储过程存储在具体的数据库，同数据库关联；

2、delimiter临时替换分隔符为//，防止语句被mysql客户端执行；

3、存储过程参数默认IN，可以显示指定[ IN | OUT | INOUT ] name type；

4、show procedure status\G，查看所有数据库的存储过程；
5、show create procedure getCnt\G，查看指定的存储过程定义；

drop procedure if exists getCnt;
delimiter //
create procedure getCnt(OUT cnt int)
begin
select count(*) INTO cnt from test;
end
//
delimiter ;
call getCnt(@cnt);
select @cnt;

九、mysql内存表

内存表特性：表结构存放在磁盘，数据存放在内存，重启数据库后数据丢失。

内存表设置：

1、set global max_heap_table_size=16384，会话变量拷贝该全局变量；

2、set max_heap_table_size=16384，单个内存表最大字节数，重启数据库后恢复默认，内存表重新加载该值；
3、show variables like '%heap%'

创建内存表：

drop table if exists temp;

create table temp(id int, name varchar(32), age int)
engine = memory;

十、mysql临时表

临时表特性：会话结束后表结构和数据都丢失，临时表一般是内存表。

临时表设置：

1、set tmp_table_size=16384;
2、show variables like 'tmp_table_size'，此变量mysql目前还没有实现；

3、show variables like 'max_tmp_tables'，此变量mysql目前还没有实现；

创建临时表：

drop table if exists temp;
create temporary table temp(id int, name varchar(32), age int)
engine = memory;