MySQL Data type concept

Characteristics of relational database

1, the data appears in tabular form

2, the names of the various records for each behavior

3, the data field corresponding to the data name for each column

4 Many rows and columns form a table

5 several forms composed of databases

Terms

Database : The collection of associated tables

Data sheet:: Table is the matrix of the data

Columns: data elements that contain the same data

Rows: tuples or records , a set of related data

Redundancy : Store twice times data, reduce performance, but improve data security (frequent switching between two tables, you can add the contents of Table 1 to table 2)

Primary key: The primary key is unique. You can use a primary key to query the data

foreign key : used to correlate two tables

Compound key : Use multiple columns as an index key

index : Use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.

referential integrity: referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that a relational model must meet in order to ensure data consistency

Data Type

A data type is a basic rule that defines what data can be stored in a column and how that data is actually stored

In practical programming, the basic principle of selecting a column's base type is: The minimum type of data that can be satisfied

String data type:

The length of the fixed-length string is specified when the table is created, does not allow more than the specified character data length, the actual storage space is as many as specified, char is afixed-length string

Variable-length string stores text with variable lengths, saved only by specified data, and text is a variable-lengthstring

Comparison: variable-length string data types are flexible, fixed-length string data types are efficient, MySQL does not allow for variable-length strings (or variable portions of a column) to be indexed

Data type	Description	Main purpose
Char	A fixed-length string of 1~255 characters whose length must be specified at creation time, otherwise MySQL is assumed to be char (1) char (n=1~255) CHAR (8), the character you enter is less than 8 o'clock, and it will be followed by a null value. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds.	Mainly used for headings, authors and other data types with small amounts
Varchar	Variable lengths of up to 255 bytes, such as when you specify varchar (n) on creation, you can store a variable-length string of 0~n characters. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.	Mainly used for indeterminate lengths such as: path, article content (Varchar (800)), etc.
Text	The text field with a maximum length of 64K and a regular size of a variable length.	Article content
Tinytext	Same text with a maximum length of 255 bytes
Meduimtext	Same text with a maximum length of 16K
Longtext	Same text with a maximum length of 4GB (plain text, usually not to 4G)
Blob	Text is case-sensitive, two blobs are not distinguished, others are the same.	A binary type can store any data, such as text, images, multimedia, and so on. Also includes Tityblob,mediumblob,longblob
Two special types of
Enum	Accepts a string from a predefined collection of up to 64K strings	enumeration, which can hold only one of the listed values or null for that type of column
SET	accepts 0 or more strings of a predefined set consisting of up to 64K strings	The value in the specified column must come from a specified value in a specific collection, for example: set (' C ', ' H ', ' P ', ' f ', ' s ', ' j ', ' A ', ' B ')

Note: Regardless of the string data type in any form, the string value must be within quotation marks (usually a single quotation mark is better), if the numeric value is used in the calculation, it should be stored in the numeric data type column, if used as a string (such as phone number, postal code) should be saved in the String data type column.

Numeric type: (int and floating point type)

Integral type

signed value: 32768 to 32767 (-2^15 to 2^15–1)  

TD style= "FONT-SIZE:16PX; line-height:1.8 "align=" center ">

data type	value range	storage space	description	main purpose
tinyint[(m)]	signed values: 128 to 127 (-2^7 to 2^7– 1)	1 bytes	very small integer	The age of the person or number of siblings, then tinyint is the most appropriate
smallint[(m)]	2 bytes	small integer	For category ID, column smallint (5)
Mediumint[(m)]	Signed values: 8388608 to 8388607 (-2^23 to 2^23–1) Unsigned values: 0 to 16777215 (0 to 2^24–1)	3 bytes	Medium integer	The "Apply to Article ID" Mediumint can represent a value of millions of and can be used for more types of values, but the storage cost is large
Int[(m)]	Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1)	4 bytes	General integer	Applies to Dates pubdate
Bigint[(m)]	Signed values: 9223372036854775808 to 9223373036854775807 (-2^63 to 2^63-1) Unsigned values: 0 to 18446744073709551615 (0 to 2^64–1)	8 bytes	Large integer

Floating point Type

data type	value range	storage space	description	main purpose
float	depends on precision	variable		double occupies twice times the space of float. Unless high-precision or wide-range values are particularly required, the data should generally be represented by a float type that uses only half the cost of storage.
float[(M, d)]	minimum non 0 value: ±1.175494351e–38	4 bytes	single precision floating point number	Equivalent to float (4), but specifies the display width and number of decimal places
Double[(M, D)]	Minimum non 0 value: ±2.2250738585072014e–308	8 bytes	Double-precision floating-point number	Equivalent to float (8), but specifies the display width and the number of decimal places
Decimal (M, D)	Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1)	4 bytes	General integer	Applies to Dates pubdate
Bigint[(m)]	M bytes (MySQL < 3.23), m+2 bytes (mysql > 3.23)	M+2	The range of values depends on M and D	Floating point number, stored in char, range depends on display width m

Date and time

data type	storage bytes	value range	description
DATE	4	1000-01-01--9999-12-31	Shown in YYYY-MM-DD format, if we want to insert the current system time, you can insert current_date or now ().
Time	3	-838:59:59--838:59:59	Although the hour range is generally 0~23, MySQL expands the hour range for time and supports negative values in order to represent certain special intervals. Assigning a value to the time type, the standard format is ' HH:MM:SS ', but not necessarily in this format. If the ' D HH:MM:SS ' format is inserted, it is similar to ' (D*24+HH): Mm:ss '. Inserting ' 2 23:50:50 ', for example, is equivalent to inserting ' 71:50:50 '. If you insert a ' hh:mm ' or ' SS ' format, the effect is to assign a value of zero to other values that are not represented by a bit. For example, insert ' 30 ', which is equivalent to inserting ' 00:00:30 ', if inserting ' 11:25 ', it is equivalent to inserting ' 11:25:00 '. In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format.
datetime	8	1000-01-01 00:00:00--9999-12-31 23:59:59
timestamp	4	19700101080001--20380119111407	timestamp value range is small, no datetime value range is large, so enter the value must be guaranteed within the range of timestamp. Its insertion is also similar to inserting other date and time data types. So how does the timestamp type insert the current time? First, you can use Current_timestamp; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP. There is a special point: the value of timestamp is associated with the time zone.
Year	1	1901--2155	There are three ways to copy the year type: the first is to insert a 4-bit string directly or 4-bit numbers, and the second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' is equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which is equivalent to insert 1970~ 1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which is the 2000; the third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) only: If you insert a number 0, it means 0000, not 2000. So when assigning a value to year type, be sure to distinguish between 0 and ' 0 ', although the difference between the two quotes, but the actual effect is indeed a difference of 2000 years.

MySQL Data type concept