Mysql data type

MySQL stores data. As long as it is data, we will specify the type of data. A certain data type is specified in the fields of the table. Then, the corresponding data type must be used in the inserted data. Also, adhere to the length requirements of the data type.

In MySQL we divide data types into the following types:

1. Numeric type (integer, floating point)
2. String type
3. Date time type
4. Composite type
5. Space type (basically not used in non-scientific work, no explanation will be given)

Integer type

MySQL data type	Bytes occupied	Value range
tinyint	1 byte	-128~127
smallint	2 byte	-32768~32767
mediumint	3 bytes	-8388608~8388607
int	4 bytes	Range -2147483648~2147483647
bigint	8 bytes	+-9.22*10 of 18 Power

#The length of the integer is different, and the actual use process is also different.

MySQL extends the SQL standard in the form of an optional display width indicator so that when a value is retrieved from the database, the value can be lengthened to a specified length. For example, specifying that the type of a field is INT(6),
ensures that values containing less than 6 digits are automatically filled with spaces when retrieved from the database. Note that using a width indicator does not affect the size of the field or the range of values it can store.

Note:

1. When creating table fields, we can use unsigned tiny integers (tinyint) to represent gender. Use 0 to represent female and 1 to represent male. Use 2 to represent unknown.
2. Similar to human age, unsigned integers can be used when creating table fields. Because human age has not yet had a negative number
3. in actual use. What is the maximum value that needs to be stored in our business. When we create a table, we choose what type to store such values.

Floating point type

MySQL data type	Bytes occupied	Value range
float(m, d)	4 bytes	Single precision floating point type, m total number, d decimal Bits
double(m, d)	8 bytes	Double precision floating point type, m total number, d decimal places
decimal(m, d)		decimal is a floating point number stored as a string

Note:

1. Floating point is an inexact value, and there may be inaccuracies
2. And decimal is called a fixed-point number. Internally, MySQL is essentially stored as a string. In actual use, if there is floating-point number storage with relatively high requirements for amount and money precision, it is recommended to use the decimal (fixed-point number) type.

Character type

MySQL data type	Bytes occupied	Value range
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-255 bytes	Variable length string
TINYBLOB	0-255 bytes	Binary string not exceeding 255 characters
TINYTEXT	0-255 bytes	Short text string
BLOB	0-65535 bytes	Long text data in binary form
TEXT	0 -65535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Medium length in binary form Text data
MEDIUMTEXT	0-16 777 215 bytes	Medium length text data
LOGNGBLOB	0-4 294 967 295 bytes	Very large text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Very large text data
VARBINARY(M)	Allows fixed-length bytes with a length of 0-M bytes String	The length of the value + 1 byte
BINARY(M)	M	Allowed length 0-M A fixed-length byte string of bytes

*CHAR *type is used for fixed-length strings and must be defined with a size modifier within parentheses. This size modifier ranges from 0-255. Values larger than the specified length will be truncated, while values smaller than the specified length will be padded with spaces.
*VARCHAR *Treat this size as the size of the value, and fill it with spaces if the length is not insufficient. The VARCHAR type treats it as the maximum value and only uses the length actually needed to store the string
The type is not padded with spaces, but values longer than the indicator are still truncated.
Because the VARCHAR type can dynamically change the length of the stored value based on the actual content, using the VARCHAR type can greatly save disk space and improve storage efficiency when you are not sure how many characters a field requires.

text type and blob typeWhen the field length requirement exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data are used to store text blocks or binary data types such as images and sound files.
There are differences in classification and comparison between TEXT and BLOB types. The BLOB type is case-sensitive, while TEXT is not case-sensitive. Size modifiers are not used on various BLOB and TEXT subtypes.

Time type

MySQL data type Bytes occupied Value range date 3 bytes Date, format: 2014-09-18 time 3 bytes Time, format: 08:42:30 datetime 8 bytes Date time, format: 2014-09-18 08:42:30 timestamp 4 bytes Automatic storage record modification Time year 1 byte Year Note:

The time type is rarely used in web systems. Many people like to use int to store time. When inserting, the unix timestamp is inserted because this method is more convenient for calculation. Use date type functions in front-end business to convert unix timestamps into time that people can recognize.
You can choose the above types according to the actual situation
Some people use the datetime type to store time in order to facilitate viewing in database management.

Composite type

An ENUM type only allows one value to be obtained from a collection; while the SET type allows any number of values to be obtained from a collection.

ENUM type

The ENUM type only allows one value to be obtained in the collection, which is somewhat similar to a single option. Easier to understand when dealing with mutually exclusive data, such as human gender. ENUM type fields can take a value from a collection or use a null value. Any other input will cause MySQL to insert an empty string into the field. In addition, if the case of the inserted value does not match the case of the values in the collection, MySQL will automatically use the case of the inserted value to convert it to a value consistent with the case of the collection.

The ENUM type can be stored as a number internally in the system, and is indexed starting from 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error information. This error value is represented by index 0 or an empty string.

MySQL considers the values appearing in the ENUM type collection to be legal input, and any other input will fail. This shows that the location of the erroneous record can be easily found by searching for rows that contain an empty string or a corresponding numeric index of 0.

SET TypeThe SET type is similar to, but not identical to, the ENUM type. The SET type can obtain any number of values from a predefined collection. And like the ENUM type, any attempt to insert a non-predefined value in a SET type field will cause MySQL to insert an empty string. If you insert a record that contains both legal and illegal elements, MySQL will retain the legal elements and remove the illegal elements.

A SET type can contain up to 64 elements. In a SET element the value is stored as a discrete sequence of "bits" that represent its corresponding element. Bits are a simple and efficient way to create ordered collections of elements.
And it also removes duplicate elements, so it is impossible to contain two identical elements in the SET type.
To find illegal records from a SET type field, just look for rows containing empty strings or binary values of 0.

Type usage

We have learned so many types, just use the corresponding type when creating a table statement.

For example:

CREATE TABLE IF NOT EXISTSdemo(
idint(11) NOT NULL,
usernamevarchar(50) NOT NULL,
passwordchar(32) NOT NULL,
contentlongtext NOT NULL,
createtimedatetime NOT NULL,
sextinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Other attribute settings of the field

UNSIGNED (unsigned)Mainly used for integer and floating point types, use unsigned. That is, there is no preceding - (minus sign).
Longer storage digits. The value range of tinyint integer is -128~127. After using unsigned, 0-255 lengths can be stored.

When creating, follow the integer or floating point field statement:

unsigned

ZEROFILL (0 filled)0 (not a space) can be used to complement the output value. Use this modifier to prevent the MySQL database from storing negative values.

When creating, follow the integer or floating point field statement:

zerofill

defaultdefault attribute ensures that When no value is available, a constant value is assigned. This value must be a constant because MySQL does not allow the insertion of function or expression values. Additionally, this property cannot be used with BLOB or TEXT columns. If the NULL attribute has been specified for this column, the default value will be NULL if no default value is specified, otherwise the default value will depend on the data type of the field.

When creating, follow the integer or floating point field statement:

default 'value'

not nullIf a column is defined as not null, null values will not be allowed to be inserted into the column. It is recommended to always use the not null attribute in important situations as it provides a basic validation that all necessary values have been passed to the query.

When creating, follow the integer or floating point field statement:

not null

nullSpecify the column With a null attribute, the column can remain empty regardless of whether other columns in the row have been filled. Remember, null means "none" to be precise, not the empty string or 0.

Do not declare not null after the integer or floating point field statement when creating.

Continuing Learning MySQL data type Explanation Example set Set type set(“member”, “member2″, … “member64″) enum Enumeration type enum(“member1″, “member2″, … “member65535″)