MySQL Data sheet design

1. Numeric class data column type

Data column type	Storage space	Description	Range of values
TINYINT	1 bytes	A very small integer	Signed Value: -128~127 Unsigned value: 0~255
SMALLINT	2 bytes	A smaller integer	Signed Value: -32768~32767 Unsigned value: 0~65535
Mediumint	3 bytes	A medium-sized integer	Signed Value: -8388608~8388607 Unsigned value: 0~16777215
Int	4 bytes	Standard integer	Signed Value: -2147483648~2147483647 Unsigned value: 0~4294967295
BIGINT	8 bytes	Large integer	Signed Value: -263~263-1 Unsigned value: 0~264-1
FLOAT	or 8 bytes	Single-precision floating-point number	min Non 0 value: + 1.175494351E-38 Maximum non-0 value: +- 3.402823466E+38

Data column type	Storage space	Description	Range of values
DOUBLE	8 bytes	Double-precision floating-point number	Minimum non 0 value: +-2.225073E-308 Maximum non-0 value: +-1.797693E+308
DECIMAL	Custom	Floating-point number expressed as a string	Depends on the number of bytes in the storage unit

Integral type considerations:

Numbers after integers (3), SMALLINT (3), and so on, do not affect the storage range of the numeric value, only the number that follows the display integer is meaningful only if it is filled with 0. The number following the integer can omit the floating point note: The number following the floating-point type rounds the number that is deposited, for example, by depositing a 1.234 in the float (6,1) data column, the result being 1.2, 6 representing the display length, and 1 representing the decimal digit length, rounding. 2. String class data column type

Data column type	Storage space	Description	Range of values
char[(M)]	M bytes	Fixed length string	M bytes
varchar[(M)]	L+1 bytes	Variable string	M bytes
Tinyblob,tinytext	L+1 bytes	Very small blob (binary large object) and text string	28-1 bytes
Blob,text	l+2 bytes	Small BLOBs and text strings	216-1 bytes
Mediumblob, Mediumtext	L+3 bytes	Medium blob and text string	224-1 bytes
Longblob, Longtext	L+4 bytes	Large BLOBs and text strings	232-1 bytes
ENUM (' value1 ', ' value2 ' ...)	1 or 2 bytes	Enumeration: Can be assigned to an enumeration member	65,535 Members
SET (' value1 ', ' value2 ' ...)	1,2,3,4 or 8 bytes	Collection: Multiple collection members can be assigned	64 members

String type considerations: the length range of char and varchar types is between 0~255 when using char and varchar types, when the actual value we pass in is longer than the specified length, the string is truncated to the specified length when we pass in the value of the char type. Length is less than the specified length, the actual length is padded to the specified length using a varchar type, if the length of the value we pass in is less than the specified length, the actual length is the length of the passed-in string, and no space is used to fill the char more efficiently than the varchar, when the space is larger than the BLOB and T The ext type is a data type that can hold any large data blob case-sensitive, text case-insensitive enum and set type are special string types whose column values must be selected from a fixed set of strings enum can only select one of the values, set can select multiple values 3, date and time Class data column type

Data column type	Storage space	Description	Range of values
DATE	3 bytes	Date value represented in "YYYY-MM-DD" format	1000-01-01~9999-12-31
Time	3 bytes	Time value represented in "HH:MM:SS" format	-838:59:59-838:59:59
Datetime	8 bytes	"Yyyy-mm-dd hh:mm:ss" format	1000-01-01 00:00:00~9999-12-31
TIMESTAMP	4 bytes	Timestamp represented by "YYYYMMDDHHMMSS" format	At some point in 19,700,101,000,000-2037 years.
Year	1 bytes	Year value in "YYYY" format	1901~2155

Date type considerations: When storing dates, we can use an integer to store timestamps, which makes it easier for us to calculate the date 4, null value NULL value considerations: null means "no value" or "Unknown value" can test whether a value is null cannot be entered into a null value The arithmetic operation of a row arithmetic calculation on a null value, the result of which is either null 0 or NULL means false, the remaining value means true 5, type conversion

In the MySQL expression, if the type of a data value does not match the type required by the context, MySQL automatically converts the data value based on the operation that will be performed. Such as:

1+ ' 2 ' #会自动转换成1 +2=3

1+ ' abc ' #会自动转换成1 +0=1

6. Data Field Properties

UNSIGNED can only be used to set numeric types, do not allow negative numbers the maximum storage length increases by one zerofill can only be used to set the numeric type, before the value is automatically used 0 to take up the insufficient number of digits auto_increment used to set the auto-growth property of the field, each Adding a record, the value of the field is automatically incremented by 1 null and NOT NULL by default, that is, when inserting a value without inserting a value in this field, the default is a null value, and if not NULL is specified, the value must be populated in this field when the value is inserted the default can use this property to specify a Default value, this value is added by default if no value is added to this column

MySQL Data sheet design