How to use DATE to realize data type storage in MYSQL
This article introduces how to use DATE to achieve data type storage in MYSQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.
MYSQL- data type storage-DATE on Engine of myisam [@ more@]
1. Environment version:
OS: LINUX AS4
MYSQL: 5.0.51a-log
ENGINE: Myisam DEFAULT CHARSET=latin1
two。 Analysis.
The date type occupies 3 bytes (24 bits), ranging from '1000-01-01' to '9999-12-31' in the format 'YYYY-MM-DD'
Where: from left to right
1x 15 bits: storage year (15 bits in total)
16-19 bits: storage month (4 digits in total)
2024-bit: storage day (5 bits in total)
For example, "2009-03-09", where:
Decimal hexadecimal binary
-
2009-> 0X7D9-> 000 0111 1101 1001
03-> 0X03-> 0011
09-> 0X09-> 01001
-
After combining the binary system: 0000 1111 1011 0010 0110 1001
-
0 F B 2 6 9
Hexadecimal: 0FB269
Note: the data in the date field is stored in reverse.
3. Verification
Here's what we can verify:
Drop table if exists heyf
Create table heyf (id int, birthday date) type myisam DEFAULT CHARSET=latin1
Insert into heyf values (12 months 2009-03-09')
System hexdump / opt/mysql/data/test/heyf.MYD
-
0000000 0cf9 0000 6900 0fb2
0000008
-
Where:
F9: marker bit
0C 00 00 00: COL1, INT = 12
69b20f: the date. Because it is reverse storage, it needs to be read in reverse: 0FB269
4. Correlation function
4.1 convert hexadecimal to decimal
Select 0xa + 0
-
ten
Or
Select conv ("a", 16,510)
-
ten
4.2 convert decimal to hexadecimal
Select conv (10, 10, 10, 16)
-
A
On how to use DATE in MYSQL to achieve data type storage is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
