mysql的存储方法（MySQL中的binary类型使用操作）

时间：2022-03-30 00:51:32类别：数据库

mysql的存储方法

MySQL中的binary类型使用操作

本文主要向大家介绍了MySQL数据库之MySQL的binary类型操作，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

示例数据表：

1

2

3 CREATE TABLE test_bin (

bin_id BINARY(16) NOT NULL

) Engine=InnoDB;

插入数据(内容是一个32位的UUID字符串值)：

INSERT INTO test_bin(bin_id) VALUES(UNHEX(‘FA34E10293CB42848573A4E39937F479‘));

INSERT INTO test_bin(bin_id) VALUES(UNHEX(?));

或

INSERT INTO test_bin(bin_id) VALUES(x‘FA34E10293CB42848573A4E39937F479‘);

查询数据：

1

2

3

4

5

6 SELECT HEX(bin_id) AS bin_id FROM test_bin;

SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = UNHEX(‘FA34E10293CB42848573A4E39937F479‘);

SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = UNHEX(?);

SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = x‘FA34E10293CB42848573A4E39937F479‘;

查询结果：

bin_id

--------------------------

FA34E10293CB42848573A4E39937F479

备注：使用MySQL内置的 UUID() 创建一个函数返回 BINARY（16）类型的UUID值

CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REPLACE(UUID(),‘-‘,‘‘));

或

CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REVERSE(REPLACE(UUID(),‘-‘,‘‘)));

使用：

INSERT INTO test_bin(bin_id) VALUES(uu_id());

范例1：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19 Connection conn = null;

Statement stat = null;

ResultSet rs = null;

try {

conn = JDBCUtils.getConnection(map);

String sql = "select HEX(RECID) AS recid,STDNAME AS stdname ,HEX(RESID) AS resid from jyyt";

stat = conn.createStatement();

rs = stat.executeQuery(sql);

while (rs.next()) {

String recid = rs.getString("recid");

String staname = rs.getString("stdname");

String resid = rs.getString("resid");

System.out.println(recid + "---" + staname + "---" + resid);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCUtils.closeConnection(conn, stat, rs);

}

范例2：

1

2

3

4

5

6

7

8

9

10

11 SELECT

y.UNITID AS unitid,

y.UNITNAME AS unitName,

y.WARNINGTYPE AS warningType

FROM

GXJT_YJ AS y

LEFT JOIN md_org AS m ON m.RECID = y.UNITID

LEFT JOIN PMS_COMPANY_INFO AS p ON m.RECID = p.UNITID

WHERE

HEX(m.parents) LIKE '%66F7B47C80000101D5E8ABF15CD9DA73%'

AND y.WARNINGTYPE = 'REGISTRATION_DIFFERENT'