Categories:
String & Binary Functions (Cryptographic Hash)

SHA1_BINARY

Returns a 20-byte binary containing the 160-bit SHA-1 message digest.

Syntax

SHA1_BINARY(<msg>)

Arguments

msg
A string expression, the message to be hashed.

Examples

SELECT sha1_binary('Snowflake');

------------------------------------------+
         SHA1_BINARY('SNOWFLAKE')         |
------------------------------------------+
 FDA76B0BCC1E87CF259B1D1E3271D76F590FB5DD |
------------------------------------------+

The data type of the output is BINARY and can be stored in a BINARY column:

Create and fill a table:

CREATE TABLE sha_table(
    v VARCHAR, 
    v_as_sha1 VARCHAR,
    v_as_sha1_hex VARCHAR,
    v_as_sha1_binary BINARY,
    v_as_sha2 VARCHAR,
    v_as_sha2_hex VARCHAR,
    v_as_sha2_binary BINARY
    );
INSERT INTO sha_table(v) VALUES ('AbCd0');
UPDATE sha_table SET 
    v_as_sha1 = SHA1(v),
    v_as_sha1_hex = SHA1_HEX(v),
    v_as_sha1_binary = SHA1_BINARY(v),
    v_as_sha2 = SHA2(v),
    v_as_sha2_hex = SHA2_HEX(v),
    v_as_sha2_binary = SHA2_BINARY(v)
    ;

Here are the query and output (note that for display, the output is implicitly cast to a user-readable form, which in this case is a string of hexadecimal digits):

SELECT v, v_as_sha1_binary
  FROM sha_table
  ORDER BY v;
+-------+------------------------------------------+
| V     | V_AS_SHA1_BINARY                         |
|-------+------------------------------------------|
| AbCd0 | 9DDB991863D53B35A52C490DB256207C776AB8D8 |
+-------+------------------------------------------+