记一次数据库mysql与tidb查询时的区别
查询时的区别
mysql查询时,会把字符串空格去掉,tidb则是等值查询 如下tidb:
存储的时候由于没有trim导致多的空格
生成业务id的区别
由于tidb是分布式数据库,所以在生成唯一id时,需要注意唯一性
例如:创建一个pageId,生成规则在mysql中,没有问题的,数据量小,但在tidb中以及在clickhouse中,需要考虑 全局唯一ID(
分布式ID)
。以下是基于雪花算法生成(SnowFlake)
/** * Twitter的SnowFlake算法,使用SnowFlake算法生成一个整数,然后转化为62进制变成一个短地址URL * * https://github.com/beyondfengyu/SnowFlake */ public class SnowFlakeShortUrl { /** * 起始的时间戳 */ private final static long START_TIMESTAMP = 1480166465631L; /** * 每一部分占用的位数 */ private final static long SEQUENCE_BIT = 12; //序列号占用的位数 private final static long MACHINE_BIT = 5; //机器标识占用的位数 private final static long DATA_CENTER_BIT = 5; //数据中心占用的位数 /** * 每一部分的最大值 */ private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT); private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT); private final static long MAX_DATA_CENTER_NUM = -1L ^ (-1L << DATA_CENTER_BIT); /** * 每一部分向左的位移 */ private final static long MACHINE_LEFT = SEQUENCE_BIT; private final static long DATA_CENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT; private final static long TIMESTAMP_LEFT = DATA_CENTER_LEFT + DATA_CENTER_BIT; private long dataCenterId; //数据中心 private long machineId; //机器标识 private long sequence = 0L; //序列号 private long lastTimeStamp = -1L; //上一次时间戳 private long getNextMill() { long mill = getNewTimeStamp(); while (mill <= lastTimeStamp) { mill = getNewTimeStamp(); } return mill; } private long getNewTimeStamp() { return System.currentTimeMillis(); } /** * 根据指定的数据中心ID和机器标志ID生成指定的序列号 * * @param dataCenterId 数据中心ID * @param machineId 机器标志ID */ public SnowFlakeShortUrl(long dataCenterId, long machineId) { if (dataCenterId > MAX_DATA_CENTER_NUM || dataCenterId < 0) { throw new IllegalArgumentException("DtaCenterId can't be greater than MAX_DATA_CENTER_NUM or less than 0!"); } if (machineId > MAX_MACHINE_NUM || machineId < 0) { throw new IllegalArgumentException("MachineId can't be greater than MAX_MACHINE_NUM or less than 0!"); } this.dataCenterId = dataCenterId; this.machineId = machineId; } /** * 产生下一个ID * * @return */ public synchronized long nextId() { long currTimeStamp = getNewTimeStamp(); if (currTimeStamp < lastTimeStamp) { throw new RuntimeException("Clock moved backwards. Refusing to generate id"); } if (currTimeStamp == lastTimeStamp) { //相同毫秒内,序列号自增 sequence = (sequence + 1) & MAX_SEQUENCE; //同一毫秒的序列数已经达到最大 if (sequence == 0L) { currTimeStamp = getNextMill(); } } else { //不同毫秒内,序列号置为0 sequence = 0L; } lastTimeStamp = currTimeStamp; return (currTimeStamp - START_TIMESTAMP) << TIMESTAMP_LEFT //时间戳部分 | dataCenterId << DATA_CENTER_LEFT //数据中心部分 | machineId << MACHINE_LEFT //机器标识部分 | sequence; //序列号部分 } }
between and
//mysql、tidb、clickhouse使用between and查询,包含两边的边界 //但是查询时间时,如果数据库存的年月日时分秒,查询用年月日,则会默认按当天的00:00:00查询,即不包含当天数据(后边界) select * from t_user where create_time between 2021-12-07 and 2021-12-08 //等同于 select * from t_user where create_time between 2021-12-07 00:00:00 and 2021-12-08 00:00:00 //使用>= <=也一样,默认时当天00:00:00不包含后界当天数据 select * from t_user where create_time >=2021-12-07 and create_time <= 2021-12-08
原文链接:https://zhuanlan.zhihu.com/p/152179727 【基于雪花算法(Snowflake)模式】