###1. java生成32位uuid和重新排序 uuid 36为 去掉-为32位
import com.google.common.base.CharMatcher;
import com.fasterxml.uuid.EthernetAddress;
import com.fasterxml.uuid.Generators;
public class UuidUtils {
public static final TimeBasedGenerator UUID_GENERATOR =
Generators.timeBasedGenerator(EthernetAddress.fromInterface());
public static String getUuidByJugOrdered() {
Logger LOGGER = LoggerFactory.getLogger(BasePojo4String.class);
UUID uuidJug = UUID_GENERATOR.generate();
String uuid = uuidJug.toString();
uuid = uuid.substring(14, 19) + uuid.substring(9, 14) + uuid.substring(19, 24) + uuid
.substring(24) + "-" + uuid.substring(0, 8);
return CharMatcher.anyOf("-").removeFrom(uuid);
}
}
转换结果示例
原始uuid 22886ea0-0048-11e5-9e24-fcaa1490706f 转换后 11e5-0048-9e24-fcaa1490706f-22886ea0
原始uuid 229c92e1-0048-11e5-9e24-fcaa1490706f 转换后 11e5-0048-9e24-fcaa1490706f-229c92e1
原始uuid 22b37642-0048-11e5-9e24-fcaa1490706f 转换后 11e5-0048-9e24-fcaa1490706f-22b37642
原始uuid 22ca80b3-0048-11e5-9e24-fcaa1490706f 转换后 11e5-0048-9e24-fcaa1490706f-22ca80b3
原始uuid 22de08b4-0048-11e5-9e24-fcaa1490706f 转换后 11e5-0048-9e24-fcaa1490706f-22de08b4
###2.mysql二进制存储 表主键字段
c_id BINARY(16) NOT NULL COMMENT 'ID',PRIMARY KEY (c_id)
binary的意思就是按原始的二进制方式存储,只存储二进制的信息
转换函数【32位字符16字节】
DELIMITER $$
DROP FUNCTION IF EXISTS toBinaryUuid $$
CREATE FUNCTION toBinaryUuid($data VARCHAR(36))
RETURNS binary(16) DETERMINISTIC NO SQL
BEGIN
DECLARE $result BINARY(16) DEFAULT NULL;
IF $data IS NOT NULL THEN
SET $data = REPLACE($data,'-','');
SET $result = UNHEX($data);
END IF;
RETURN $result;
END
$$
DROP FUNCTION IF EXISTS toUuid $$
CREATE FUNCTION toUuid($data BINARY(16)) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
DECLARE $result CHAR(36) DEFAULT NULL;
IF $data IS NOT NULL THEN
SET $result = HEX($data);
END IF;
RETURN $result;
END
$$
DELIMITER ;
-- 测试
SELECT toBinaryUuid('11e500322d38f45fa67dfcaa1490706f');
SELECT toUuid(toBinaryUuid('11e500322d38f45fa67dfcaa1490706f'));
###3.测试数据 测试表
DROP TABLE IF EXISTS poi_action_data CASCADE;
CREATE TABLE poi_action_data (
c_id BINARY(16) NOT NULL COMMENT 'ID',
c_user_id VARCHAR (80) NOT NULL COMMENT '用户ID',
c_product_id VARCHAR (32) NOT NULL COMMENT '产品ID',
c_project_id VARCHAR (32) COMMENT '项目ID',
c_type CHAR (1) NOT NULL COMMENT '动作类型,a=行为动作action,b=业务动作biz',
c_action_id VARCHAR (32) NOT NULL COMMENT '动作编码',
c_value VARCHAR (2000) COMMENT '指标值',
c_time TIMESTAMP COMMENT '修改时间',
c_product_version VARCHAR (32) COMMENT '产品版本',
c_log_id VARCHAR (32) NOT NULL COMMENT '日志ID',
c_json VARCHAR (500) COMMENT 'json格式扩展信息',
c_tag1 VARCHAR (32) COMMENT '冗余字段1',
c_tag2 VARCHAR (32) COMMENT '冗余字段2',
PRIMARY KEY (c_product_id,c_time,c_id)
-- ,CONSTRAINT poi_ad_log_id FOREIGN KEY (c_log_id) REFERENCES poi_action_log (c_id) ON DELETE RESTRICT
) ENGINE = INNODB COMMENT = '解析后的动作数据,时长、次数、业务量等';
插入数据、查询数据
INSERT INTO poi_action_data
(c_id, c_user_id, c_product_id, c_project_id, c_type, c_action_id, c_value, c_time, c_product_version, c_log_id, c_json, c_tag1, c_tag2)
VALUES (uuidToBinary('11e5003f0da3b434b5e7fcaa1490706f'), '1', '17', NULL, 'a', '12', '1', '2015-04-13 11:06:57', '10', '11e5003f0a850f10b5e7fcaa1490706f', NULL, NULL, NULL);
SELECT toUuid(c_id) FROM poi_action_data;
###4. mysql uuid
DELIMITER $$
DROP FUNCTION IF EXISTS toOrderedUuid $$
CREATE FUNCTION toOrderedUuid(uuid BINARY(36))
RETURNS binary(16) DETERMINISTIC
RETURN UNHEX(CONCAT(SUBSTR(uuid, 15, 4),SUBSTR(uuid, 10, 4),SUBSTR(uuid, 1, 8),SUBSTR(uuid, 20, 4),SUBSTR(uuid, 25)))$$
DELIMITER ;
-- SELECT toOrderedUuid(UUID()),LENGTH(toOrderedUuid(UUID()));