Inserting hex value in MySQL

Question

I have created an SQL database using Java. I have a table created which has two columns, the first being a big integer which increments, the second I have tried defining it as a char, varchar and binary.

But I'm still not getting the desired functionality. Say I try and store a hex number 0a into the char column and I get an error. I appended 0x to the beginning and it seems to store, but when I print out the contents it is blank. Or in some cases I get characters such as '/' or '?'. I also tried using SQL explorer and it gives me the same result viewing the table,

My problem is I need to store an eight character hex string such as eb8d4ee6.

Could someone please advise me of how this can be done?

Microphysics

Answer 1

See http://dev.mysql.com/doc/refman/5.5/en/hexadecimal-literals.html

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. For values written using X'val' or x'val' format, val must contain an even number of digits. For values written using 0xval syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0x0a and 0xaaa are interpreted as 0x0a and 0x0aaa.

In numeric contexts, hexadecimal values act like integers (64-bit precision). In string contexts, they act like binary strings, where each pair of hex digits is converted to a character:

You probably should store the Hex number in an integer column. You can then convert back to hex when selecting using the HEX() function.

E.g.,

INSERT INTO MyTable (`MyIntegerColumn`) VALUES (0xeb8d4ee6);

Rattle

Hi thanks for quick reply i have read this article, as i said im an sql noob, but i have tried to append 0x defining it as hex but when viewing the table the value is not there? – Microphysics

@user524156: Try defining the column as type integer. – Chiliarch

this is my sql statment creating the table – Microphysics

String sql = "CREATE TABLE Table1(encrypt_key BIGINT NOT NULL AUTO_INCREMENT,encrypt_val VARCHAR(10),primary key (encrypt_key) )"; – Microphysics

@user524156: see my example INSERT. – Chiliarch

Ok integer wont work as the hex i need to store is 8 chars long, such as df0e8d7e, i used BIGINT and it stores but stores this as -552694402, this is not decimal for the hex number i inserted im not sure what it is, is there anyway just to the hex as is?, i will also need to search and match these values later if thats an issue when choosing how to save it. – Microphysics

Ok, I tested using BIGINT (INSERT INTO MyTable` (MyBigIntColumn) VALUES (0xDF0E8D7E);). Selecting my inserted value back gave 3742272894, and using the HEX()` function on it returned DF0E8D7E1. Seems to work fine, not sure where your problem is. – Chiliarch

Answer 2

You can use a Json column: And use JSON.stringify(hex) to insert and you can always get the result via select and compare too

Rozier