Beckham _ mysql table creation and column attributes, and Beckham _ mysql column attributes

Source: Internet
Author: User

Beckham _ mysql table creation and column attributes, and Beckham _ mysql column attributes
Mysql table creation and column attributesBrief:
I. Table Creation Principles
Ii. Detailed column attribute descriptions

 

I. Table Creation Principles

Table creation: in fact, it is the process of declaring columns. data is stored on the hard disk (memory) as files)

Column: the space occupied by different column types is different. The principle of selecting columns: sufficient, no waste

 

Ii. Description of column types and attributes

Column types: integer, floating point, seasonal, and date/time

2.1 integer

Tinyint/smallint/mediumint/int/bigint

2.1.1. Explanation: one byte of tinyint

[] [] [] [] [] [] [] [] [] A total of 8 digits

Assume that all values 8 are 0 =, and convert them to decimal 0.

Assume that all values of 8 are 1 = and are converted to 2 ^ 8-1 in decimal format.

The computer can also store negative numbers and use the highest bit as the symbol bit.

0 indicates a positive number. 1 indicates a negative number.

When the highest bit is 0:

[0] [] [] [] [] [] [] [] [] => 0 ~ 2 ^ 7-1 convert to decimal 0 ~ 127

When the highest bit is 1

[1] [] [] [] [] [] [] [] [] => 0 ~ -2 ^ 7 to decimal 0 ~ -128

Column

Bytes

Unsigned

Signed

Tinyint

1

0 ~ 255

-128 ~ 127

Smallint

2

0 ~ 2 ^ 16-1

-2 ^ 15 ~ 2 ^ 15

Medium

3

0 ~ 2 ^ 24-1

-2 ^ 23 ~ 2 ^ 23

Int

4

0 ~ 2 ^ 32-1

-2 ^ 31 ~ 2 ^ 31

Bigint

8

0 ~ 2 ^ 64-1

-2 ^ 63 ~ 2 ^ 63

2.1.2. Instance

2.1.3. Optional attribute of integer columns: not null unsigned zerofill

Summary:

A. When an integer column needs to be filled with 0, it must be unsigned.

B. The value of 0 is not changed when it is filled.

 

2.2 floating point type

For the floating-point column type, the single-precision value in MySQL uses 4 bytes, and the double-precision value uses 8 bytes.

Difference between float and decimal: decimal is more accurate

 

2.3. Balanced type

Char: Fixed Length char (M), M represents the width, can accommodate the number of characters

Varchar: variable-length char (M), M represents the width, can accommodate the number of characters

Differences:

A. for efficiency, char <= 1 varchar <1

That is, char can be fully utilized, but varchar won't. Compared with CHAR, The VARCHAR value only saves the number of characters and adds a byte to record the length.

B. For the tail (from the left to the right), char will filter the trailing space, but varchar will not. Because char stores less than the defined character length, it will fill in spaces at the end of the display, and then remove spaces at the end of the display.

Case:

CREATETABLE vc (v VARCHAR (4), c CHAR (4 ));

INSERTINTO vc VALUES ('AB', 'AB ');

SELECTCONCAT (v, '+'), CONCAT (c, '+') FROM vc;

Byte problems:

Varchar (N), where N refers to the number of characters, not the number of bytes. The number of bytes occupied is related to encoding.

UTF-8, a Chinese character 3-byte English letter 1 byte

MySQL 5.0 or later versions:

1. The length of a Chinese character is related to the encoding:

UTF-8: a Chinese character = 3 bytes

GBK: one Chinese Character = 2 bytes

2. varchar (n) indicates n characters. Mysql can store n characters regardless of Chinese characters and English characters. The actual length is different.

3. MySQL check length, available in SQL language:

Select LENGTH (fieldname) from tablename to view

2.4. Date and Time

2.4.1, year


Summary

MySQL retrieves and displays the YEAR Value in YYYY format. The range is 1901 to 2155.

YEAR values in various formats can be specified:

· A four-character string in the range of '20180101' to '20180101 '.

· Four digits in the range of 1901 to 2155.

· Two strings in the range of '00' to '99 '. Values from '00' to '69' and from '70' to '99' are converted to YEAR values in the range of 2000 to 2069 and 1970 to 1999.

· Two integers in the range of 1 to 99. Values ranging from 1 to 69 and 70 to 99 are converted to YEAR values in the range of 2001 to 2069 and 1970 to 1999. Note that the range of two integers is slightly different from that of two strings, because you cannot directly set zero as a number and interpret it as 2000. You must specify it as a string '0' or '00' or it is interpreted as 0000.

2.4.2. time

MySQL retrieves and displays TIME values in 'hh: MM: ss' format (or uses 'hhh: MM: ss' format for large hour values ). The TIME value can range from '-838: 59: 59' to '2017: 59: 59 '. The reason for this is that the TIME type is not only used to indicate the TIME of a day (must be less than 24 hours ), it may also be the past time of an event or the interval between two events (which can be greater than 24 hours or even negative ).

2.4.3, date

Use the DATE type when you only need the DATE value instead of the time part. MySQL retrieves and displays DATE values in 'yyyy-MM-DD 'format. The supported range is '2017-01-01 'to '2017-12-31 '.

2.4.4. datetime

Summary: Although mysql has time and date types. However, when the development process involves the column type of time, int is usually used as the integer type. It mainly takes into account the convenience of development and the high shaping efficiency.


The quieter you become, the more you are able to hear!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.