Binary Type Operations in MySQL

This article mainly introduces the binary type operations of MySQL database, and presents it to you through specific content. I hope it will be helpful for you to learn MySQL database.

Example data table:

CREATE TABLE test_bin (
  bin_id BINARY(16) NOT NULL
) Engine=InnoDB; 
 

Insert data (the content is a 32-bit UUID string value):

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

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

or

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

Query data:

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';

Query results:

bin_id

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

FA34E10293CB42848573A4E39937F479

Note: Use the MySQL built-in UUID() to create a function that returns a UUID value of type BINARY(16).

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

or

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

use:

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

Example 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);
 }

Example 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() function is not used:

Use the HEX() function:

Supplementary knowledge: [MySQL] How to use Navicat to view the contents of varbinary variables in the MySQL database?

environment

Navicat software version: Navicat premium 11.1.13 (64-bit)

MySQL database version: 5.7

Problem

Just as the title says.

step

The solution is simple, memo.

1. The design of the database table is as follows. The photo variable type in the table is varbinary.

2. Navicat software displays garbled characters, as shown below.

3. Right-click "Save Data As" and save it as 1.txt. The file name is arbitrary.

4. Open it with UE, and the display is as follows.

The above article on the use of binary type in MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.