MySQL数据库之MySQL的binary类型操作

1 篇文章 0 订阅
1 篇文章 0 订阅

MySQL数据库之MySQL的binary类型操作 

JDBCUtils

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

示例数据表: 
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‘); 
 
查询数据: 
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:

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:

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'

未使用HEX()函数:

使用HEX()函数:

 
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值