The SHA2 SQL Function in MySQL and MariaDB - SHA-2 checksum |
|
| | SHA2 | Syntax: | SHA2(String, Length) | | Return value: | CHAR | | Function type: | Encryption & Compression | |
| | The SHA2() function calculates a SHA-2 checksum for the "String".
The SHA-2 is considered cryptographically more secure than a SHA-1 160-bit checksum.
The SHA-2 family includes SHA-224, SHA-256, SHA-384 and SHA-512. The "Length" must be one of these values: 224, 256 or 0, 384 or 512, otherwise the function returns NULL.
The value is returned as a string of hexadecimal digits, the length of which changes depending on the precision selected, specified in "Length".
Numeric values as an argument are interpreted as a character string.
If the value of the "String" argument is NULL, the SHA2() function returns NULL.
If the value of the "Length" argument is NULL, the SHA2() function in MySQL 8.0 returns NULL, MariaDB returns the value for SHA-256.
SHA2 only works if MariaDB is configured with TLS support or MySQL with SSL support. | SQL Examples for the SHA2 function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
| select sha2('Test', 224), char_length(sha2('Test', 224)) as `chars`, bit_length(sha2('Test', 224)) as `bits`;
select sha2('Test', 256), char_length(sha2('Test', 256)) as `chars`, bit_length(sha2('Test', 256)) as `bits`;
select sha2('Test', '256');
select sha2('Test', 0);
select sha2('Test', 384), char_length(sha2('Test', 384)) as `chars`, bit_length(sha2('Test', 384)) as `bits`;
select char_length(sha2('Test', 512)), bit_length(sha2('Test', 512)) as `bits`;
select sha2(123456, 256);
select sha2('', 256);
select sha2('Test', 160);
select sha2('Test', 220);
select sha2(null, 256);
select sha2('Test', null);
|
|
| sha2('Test', 224) |  |
|
| chars | |
| bits |
|---|
| varchar(56) BINARY | int(10) | bigint(11) |
|---|
| 3606346815fd4d491a92649905a40da025d8cf15f095136b19f37923 | 56 | 448 |
|
|
| sha2('Test', 256) | |
|
| chars | |
| bits |
|---|
| varchar(64) BINARY | int(10) | bigint(11) |
|---|
| 532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25 | 64 | 512 |
|
|
| sha2('Test', '256') |
|---|
| varchar(64) BINARY |
|---|
| 532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25 |
|
|
| sha2('Test', 0) |
|---|
| varchar(64) BINARY |
|---|
| 532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25 |
|
|
| sha2('Test', 384) | |
|
| chars | |
| bits |
|---|
| varchar(96) BINARY | int(10) | bigint(11) |
|---|
| 7b8f4654076b80eb963911f19cfad1aaf4285ed48e826f6cde1b01a79aa73fadb5446e667fc4f90417782c91270540f3 | 96 | 768 |
|
|
| char_length(sha2('Test', 512)) | |
| bits |
|---|
| int(10) | bigint(11) |
|---|
| 128 | 1024 |
|
|
| sha2(123456, 256) |
|---|
| varchar(64) BINARY |
|---|
| 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92 |
|
|
| sha2('', 256) |
|---|
| varchar(64) BINARY |
|---|
| e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 |
|
|
| sha2('Test', 160) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| sha2('Test', 220) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| sha2(null, 256) |
|---|
| varchar(64) BINARY |
|---|
| NULL |
|
|
| sha2('Test', null) |
|---|
| varchar(64) BINARY |
|---|
| 532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25 |
|
|
| Example MySQL 8.0 with "Length" NULL |
| select sha2('Test', null);
|
|
| sha2('Test', null) |
|---|
| varchar(64) BINARY |
|---|
| NULL |
|
| | The examples were created with the MyWAY SQL manager: | How to use the SHA2() function in MySQL and MariaDB databases | | Both MySQL and MariaDB provide the SHA2() function, which is used to calculate hash values using the SHA-2 family of algorithms. The desired bit length for the hash can be selected, which offers more flexibility and stronger hashing options compared to the outdated SHA-1. The security level and the length of the resulting hash can be selected by specifying the bit length 256 or 512. Common use cases for the feature include secure password storage, data integrity verification, digital signatures and cryptographic operations. | | Further MySQL und MariaDB SQL Encryption & Compression | |
| | | More information about the SHA2 SQL function: and |
|
|
|
|
