type of data
In MySQL we divide data types into the following types:
1. Numeric type (integer, floating point)
2. String type
3. Date and time type
4. Composite type
5. Space type (basically not used in non-scientific work, no explanation will be given)
Type
The length of the integer type 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 a field's type is INT(6),
ensures that values containing fewer than 6 digits are automatically padded 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. The same applies to human age. Unsigned integers can be used when creating table fields. Because human age does not have 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
Note:
1. Floating point is inexact Value, 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
##
*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, on the other hand, treats it as the maximum value and only uses the length actually required to store the string. The
type will not be padded with spaces, but values longer than the indicator will still be 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 type When the field length requirement exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes depending 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
Composite type
ENUM TYPE
The ENUM type only allows one value to be obtained in the collection, which is 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 type The SET type is similar to the ENUM type but not the same. 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.
If you want to find illegal records from a SET type field, just look for rows that contain 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.
Examples are as follows:
CREATE TABLE IF NOT EXISTS demo ( id int(11) NOT NULL, username varchar(50) NOT NULL, password char(32) NOT NULL, content longtext NOT NULL, createtime datetime NOT NULL, sex tinyint(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Other attribute settings of fields
UNSIGNED (unsigned) is mainly used for integer and floating point types, use unsigned. That is, there is no preceding - (minus sign).
The number of storage bits is longer. 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 padding) 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
default The default attribute ensures that a constant is assigned when no value is available. Value, 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 null If 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
null When specifying the null attribute for a column, the column can remain empty, and Regardless of whether other columns in the row are 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.
