uuid采用char(32)或char(36)存储的话，需要占用32或36个字节。为节省存储空间，改为binary(16)，占用16字节。对于500W行的表，可节省7.4G的空间。
mybatis中没有默认的type handler来完成uuid类型<->binary类型的相互转换，需要自定义一个type handler。下面就详细地介绍如何实现。
mybatis 3.5.3
mysql 5.7.21
java 8
1 public class UuidHandler extends BaseTypeHandler {
2
4 @Override
5 public void setNonNullParameter(PreparedStatement ps,
6 int columnIndex, UUID uuid,
7 JdbcType jdbcType) throws SQLException {
8
9 long mostBit = uuid.getMostSignificantBits();
10 byte[] bytes = new byte[16];
11 bytes[0] = (byte) ((mostBit >> 56) & 0xFF);
12 bytes[1] = (byte) ((mostBit >> 48) & 0xFF);
13 bytes[2] = (byte) ((mostBit >> 40) & 0xFF);
14 bytes[3] = (byte) ((mostBit >> 32) & 0xFF);
15 bytes[4] = (byte) ((mostBit >> 24) & 0xFF);
16 bytes[5] = (byte) ((mostBit >> 16) & 0xFF);
17 bytes[6] = (byte) ((mostBit >> 8) & 0xFF);
18 bytes[7] = (byte) (mostBit & 0xFF);
19 //
20 long leastBit = uuid.getLeastSignificantBits();
21 bytes[8] = (byte) ((leastBit >> 56) & 0xFF);
22 bytes[9] = (byte) ((leastBit >> 48) & 0xFF);
23 bytes[10] = (byte) ((leastBit >> 40) & 0xFF);
24 bytes[11] = (byte) ((leastBit >> 32) & 0xFF);
25 bytes[12] = (byte) ((leastBit >> 24) & 0xFF);
26 bytes[13] = (byte) ((leastBit >> 16) & 0xFF);
27 bytes[14] = (byte) ((leastBit >> 8) & 0xFF);
28 bytes[15] = (byte) (leastBit & 0xFF);
29 //
30 ps.setBytes(columnIndex, bytes);
31 }
32
33 @Override
34 public UUID getNullableResult(ResultSet rs, String columnName) throws SQLException {
35 byte[] bytes = rs.getBytes(columnName);
36
37 long mostBit = ((((long) bytes[0] & 0xFF) << 56)
38 | (((long) bytes[1] & 0xFF) << 48)
39 | (((long) bytes[2] & 0xFF) << 40)
40 | (((long) bytes[3] & 0xFF) << 32)
41 | (((long) bytes[4] & 0xFF) << 24)
42 | (((long) bytes[5] & 0xFF) << 16)
43 | (((long) bytes[6] & 0xFF) << 8)
44 | (((long) bytes[7] & 0xFF)));
45
46 long leastBit = ((((long) bytes[8] & 0xFF) << 56)
47 | (((long) bytes[9] & 0xFF) << 48)
48 | (((long) bytes[10] & 0xFF) << 40)
49 | (((long) bytes[11] & 0xFF) << 32)
50 | (((long) bytes[12] & 0xFF) << 24)
51 | (((long) bytes[13] & 0xFF) << 16)
52 | (((long) bytes[14] & 0xFF) << 8)
53 | (((long) bytes[15] & 0xFF)));
54 return new UUID(mostBit, leastBit);
55 }
56
57
58 @Override
59 public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
60 // 略
61 return null;
62 }
63
64 @Override
65 public UUID getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
66 // 略
67 return null;
68 }
69 } 以 上代码有2个关键点：
在setNonNullParameter()方法中，将UUID转换成byte[]。UUID使用128位（16字节）存储，其中，uuid.getMostSignificantBits()返回高64位，uuid.getLeastSignificantBits()返回低64位。
在getNullableResult()方法中 , 将byte[]转换成UUID。
4.1 表
1 CREATE TABLE `user_uuid` (
2 `id` binary(16) NOT NULL,
3 `name` varchar(10) DEFAULT NULL,
4 PRIMARY KEY (`id`)
5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;4.2 实体
public class User {
private UUID id;
private String name;
// 略
}4.3 mapper
1 public interface UserMapper {
2
3 void insert(User record);
4
5 User selectByPrimaryKey(UUID id);
6
7 List selectAll();
8
9 }
1 <insert id="insert" parameterType="leo.domain.User">
2 insert into user_uuid (id, name)
3 values (#{id}, #{name})
4 insert>
5
6 <select id="selectByPrimaryKey" resultType="leo.domain.User"
7 parameterType="java.util.UUID">
8 select id, name
9 from user_uuid
10 where id = #{id}
11 select>
12
13
14 <select id="selectAll" resultType="leo.domain.User">
15 select id, name
16 from user_uuid
17 select>4.4 配置
1
2 leo.handler.UuidHandler"/>
3 在typeHandlers节点中注册UuidHandler4.5 测试用例
1 @FixMethodOrder(MethodSorters.NAME_ASCENDING)
2 public class UserMapperTest {
3
4 SqlSession session;
5 UserMapper mapper;
6
7 @Before
8 public void init() {
9 InputStream inputStream = App3_uuid.class.getResourceAsStream("/mybatis-config.xml");
10 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
11 session = sqlSessionFactory.openSession();
12 mapper = session.getMapper(UserMapper.class);
13 }
14
15
16 @Test
17 public void test1Insert() {
18 // 插入用户1
19 UUID uuid1 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba1");
20 User user1 = new User();
21 user1.setId(uuid1);
22 user1.setName("用户1");
23 mapper.insert(user1);
24 session.commit();
25
26 // 插入用户2
27 UUID uuid2 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba2");
28 User user2 = new User();
29 user2.setId(uuid2);
30 user2.setName("用户2");
31 mapper.insert(user2);
32 session.commit();
33
34 }
35
36 @Test
37 public void test2SelectByPrimaryKey() {
38 UUID uuid1 = UUID.fromString("0eaa9bf6-0c70-4678-8658-8737d3a5eba1");
39 User user1 = mapper.selectByPrimaryKey(uuid1);
40 System.out.println("user = " + user1);
41 }
42
43 @Test
44 public void test3SelectAll() {
45 List list = mapper.selectAll();
46 System.out.println("list = " + list);
47
48 }
49 }
