🔖杂记

IP函数

SELECT INET_ATON( '127.0.0.1' ) AS IP;
SELECT INET_NTOA( 2130706433 ) AS IP;

经纬度函数

**_st_distance_sphere_**

SELECT st_distance_sphere ( POINT (103.71878, 36.10396), POINT (116.23128, 40.22077) )

函数介绍如图：

_**st_distance**_

SELECT st_distance ( POINT (103.71878, 36.10396), POINT (116.23128, 40.22077) )

函数介绍如下图：

毫秒转时分秒

• Java

  DateUtil.secondToTime(毫秒值);

• MySQL

  SEC_TO_TIME(毫秒值);

  MySQL字段合并

GROUP_CONCAT('field')

选择性插入或更新

ON DUPLICATE KEY UPDATE

INSERT INTO tb_name(id, colum...) VALUES(id_value, colum_value...) 
ON DUPLICATE KEY UPDATE colum = colum_value...

REPLACE INTO… VALUES…

REPLACE INTO tb_name(id, colum...) VALUES(id_value, colum_value...)

MySQL数据处理函数

MySQL与当前时间比较

①DATEDIFF(expr1, expr2)

 表示expr1比expr2多几天，即expr1 - expr2。返回结果的单位是天。
入参不严格、可自动转换为天。

②TIMEDIFF(expr1, expr2)

 表示expr1 - expr2。返回结果是时间表达式hh:mm:ss，但如果入参有毫秒，则返回结果也会包括毫秒

③TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

 表示datetime_expr2 - datetime_expr1，注意是参数2在前，与上面两个函数是反过来的。返回一个整数数字，单位由unit参数指定

 单位支持MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

④特定比较函数

 DAYOFYEAR(expr)：获取指定时间是当年的第几天

 WEEKOFYEAR(expr)：获取指定时间是当年第几周

MySQL删除数据的区别

delete

1. DELETE属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger
2. 在 InnoDB 中，DELETE其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见
3. DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效
4. delete from table_name 删除表的全部数据,对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间
5. 对于delete from table_name where ? = ? 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间
6. delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作

7. delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间

   truncate

8. truncate：属于数据库DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger

9. truncate table table_name 立刻释放磁盘空间 ，不管是 InnoDB和MyISAM

10. truncate能够快速清空一个表。并且重置auto_increment的值

    drop

11. drop：属于数据库DDL定义语言，同Truncate

12. drop table table_name 立刻释放磁盘空间 ，不管是 InnoDB 和 MyISAM; drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态

    MySQL将一行拆分为多行

    SELECT
     SUBSTRING_INDEX( SUBSTRING_INDEX( sa.path, '.', ht.help_topic_id + 1 ), '.',- 1 ) AS path 
    FROM
     sys_sell_area AS sa
     JOIN mysql.help_topic AS ht ON ht.help_topic_id < ( length( sa.path ) - length( REPLACE ( sa.path, '.', '' ) ) + 1 ) 
    WHERE
     sa.del_flag = 0 LIMIT 1,1

    MySQL中文的字段列排序

select colum_name from tb_name order by convert(colum_name using gbk);

处理decimal类型小数位值为0

-- 处理前
SELECT '0.12000','1.203010','-0.20','10000.1000'
-- 处理后
SELECT 0+CAST('0.12000' AS CHAR),0+CAST('1.203010' AS CHAR),0+CAST('-0.20' AS CHAR), 0+CAST('10000.1000' AS CHAR)