mysql日常整理

mysql的基本数据类型里几个int如下：

类型大小范围（有符号）范围（无符号）用途

TINYINT 1字节 (-128，127) (0，255) 小整数值

SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

整数类型字节范围（有符号）范围（无符号）用途

TINYINT 1字节 (-128，127) (0，255) 小整数值

SMALLINT 2字节 (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

FLOAT 4字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值

DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值

DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值依赖于M和D的值小数值

date为需要处理的参数(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串

后面的 '%Y%m%d' 主要是将返回值格式化

例如:

mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' )

->20071120

mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d' )

->2007年11月20

UNIX_TIMESTAMP()是与之相对正好相反的时间函数

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

mysql日期和字符相互转换方法

date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();

str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();

%Y：代表4位的年份

%y：代表2为的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)

%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)

%k：代表小时,格式为(0……23)

%h：代表小时,格式为(01……12)

%I：代表小时,格式为(01……12)

%l ：代表小时,格式为(1……12)

%i：代表分钟, 格式为(00……59)

%r：代表时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表时间,格式为24 小时(hh:mm:ss)

%S：代表秒,格式为(00……59)

%s：代表秒,格式为(00……59)

——————————————————————————————————————————————————————————

日期比较示例：

SELECT * FROM test_test1 where birth_daetime='2013/06/28 17:47:52'

SELECT * FROM test_test1 where birth_day='2013/06/28'

SELECT * FROM test_test1 where birth_day='2013-06-28'

SELECT * FROM test_test1 where birth_daetime=str_to_date('2013-06-28 17:47:52', '%Y-%m-%d %H:%i:%s')

SELECT * FROM test_test1 where birth_day=str_to_date('2013/06/28', '%Y/%m/%d')

SELECT * FROM test_test1 where birth_day=str_to_date('2013-06-28 00:00:00', '%Y-%m-%d %H:%i:%s')

SELECT a.name, DATE_FORMAT(a.checktime,'%Y-%m-%d'), MIN(a.checktime),

str_to_date(concat(DATE_FORMAT(a.checktime,'%Y-%m-%d'),' 08:',FLOOR(20 + (RAND() * 30)),':00'),'%Y-%m-%d %H:%i:%s'),

FLOOR(20 + (RAND() * 30))

FROM `INOUTDATA03.15` a

GROUP BY a.name, DATE_FORMAT(a.checktime,'%Y-%m-%d')

mysql数值处理函数floor与round 在mysql中，当处理数值时，会用到数值处理函数，如有一个float型数值2.13，你想只要整数2，那就需要下面的函数floor与round。

floor：函数只返回整数部分，小数部分舍弃。

round：函数四舍五入，大于0.5的部分进位，不到则舍弃。与floor不同。

rownum

set @x=0

select @x:=ifnull(@x,0)+1 as rownum,b.id,a.id,localtime from ya_disease a,ya_doctor b;

POINT

st_distance(point(113.327955,23.129717),point)*111195 坐标point(113.327955,23.129717)与point的距离

mysql 5.6.1 加入了空间数据支持功能，新增了st_*相关函数，可以非常方便的计算两个地理坐标点的距离了。

如下例子：按我的坐标计算周边坐标的距离并由近到远排序

select name,st_distance(point(113.327955,23.129717),point)*111195 as distance,address from table1 where st_distance(point(113.327955,23.129717),point)*111195 < 100 order by distance asc limit 100

注意：其中point字段类型是 point，其值可以通过以下方法写入：

update table1 set point = point(113.123232,24.1324234)

st_distance 计算的结果单位是度，需要乘111195（地球半径6371000*PI/180）是将值转化为米

mysql5.7之前解析json字符串

SUBSTRING_INDEX(
REPLACE (
column_name, CONCAT(SUBSTRING_INDEX(column_name, '"解析的字段名":', 1),'"解析的字段名":"'),''),'"',1
)