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:
- Numeric type (integer, floating point)
- String type
- Date time type
- Composite type
- 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:
- 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.
- Similar to human age, unsigned integers can be used when creating table fields. Because human age has not yet had a negative number
- 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:
- Floating point is an inexact value, and there may be inaccuracies
- 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 situationSome people use the datetime type to store time in order to facilitate viewing in database management.
Composite type
MySQL data type | Explanation | Example |
set | Set type | set(“member”, “member2″, … “member64″) |
enum | Enumeration type | enum(“member1″, “member2″, … “member65535″) |
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 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 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 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
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.
Next Section- Chapter1Why choose this course to learn PHP
- Why learn PHP?
- What is PHP
- You can learn even with z...
- Why can't some people lea...
- Chapter2PHP environment installation
- What is the development e...
- Windows environment insta...
- Linux environment install...
- Other development environ...
- Tool selection for writin...
- Chapter3php basic syntax
- PHP basic syntax
- Our first piece of PHP co...
- Variables in php - you wi...
- echo display command
- Learning php annotations
- Data types are not myster...
- PHP integer type is an in...
- PHP data type Boolean (ac...
- PHP data type string
- PHP data type floating po...
- PHP flow control if else ...
- PHP data type NULL type
- php data type array
- Resource type of php data...
- PHP data type viewing and...
- Automatic conversion and ...
- Object (will learn later)
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- Variable references for P...
- PHP basic syntax arithmet...
- PHP basic syntax assignme...
- PHP basic syntax: self-in...
- PHP basic syntax comparis...
- Logical operations of php...
- PHP basic syntax bit oper...
- PHP basic syntax: ternary...
- Chapter4PHP process control
- Process control in PHP
- PHP process control if co...
- PHP flow control if state...
- Nested if...else...elseif...
- Multiple nesting of if st...
- Use of branch structure s...
- Use of loop statements in...
- while loop
- The difference between do...
- PHP flow control for loop...
- PHP flow control goto syn...
- Chapter5Basic function syntax of PHP
- Basic function syntax of ...
- PHP function basic syntax...
- PHP custom function callb...
- PHP custom function varia...
- PHP custom function anony...
- Internal function of php ...
- Variable scope of php cus...
- Reference to parameters o...
- PHP custom function recur...
- Static variables of php c...
- php uses system built-in ...
- php file contains functio...
- PHP math commonly used fu...
- PHP function to obtain pe...
- php date validation funct...
- PHP gets localized timest...
- PHP program execution tim...
- PHP string common functio...
- Chapter6PHP arrays and data structures
- PHP arrays and data struc...
- php array definition
- PHP array calculation
- php for loop traverses in...
- php foreach traverses as...
- PHP list, each function t...
- PHP commonly used array m...
- Common functions for php ...
- Chapter7Regular expressions in PHP
- Regular expressions in PH...
- Delimiter expressed by ph...
- Atoms in php regular expr...
- Metacharacters in php reg...
- Pattern modifiers in php ...
- Tips and commonly used re...
- PHP uses regular expressi...
- Chapter8php file system
- File system
- php read file
- php creates and modifies ...
- php creates temporary fil...
- php move, copy and delete...
- php detect file attribute...
- Common functions and cons...
- php file locking mechanis...
- php directory processing ...
- php file permission setti...
- php file path function
- PHP implements file guest...
- PHP implementation exampl...
- Chapter9PHP file upload
- PHP file upload
- When uploading files, you...
- Steps to upload php files
- Precautions for php file ...
- php completes file upload...
- php multiple file upload
- PHP file upload progress ...
- Chapter10PHP image processing
- PHP image processing
- PHP image processing gd2 ...
- PHP uses image processing...
- PHP development verificat...
- php image scaling and cro...
- PHP image watermark proce...
- Chapter11PHP error handling
- Error handling
- PHP error handling prohib...
- PHP error handling error ...
- PHP error handling error ...
- PHP error handling custom...
- Chapter12Getting started with MySQL
- Getting Started with MySQ...
- Mysql database introducti...
- Mysql entertainment expla...
- mysql database installati...
- Data statement operation ...
- Mysql connect to database
- Mysql database operation
- Mysql data table operatio...
- Mysql data field operatio...
- Mysql data type
- Mysql character set
- Mysql table engine
- Mysql index
- Mysql add, delete, modify...
- Mysql add, delete, modify...
- Mysql multi-table joint q...
- Mysql addition, deletion,...
- Mysql add, delete, modify...
- DCL statement
- Learn commonly used Engli...
- Chapter13PHP operates mysql database
- PHP operates mysql databa...
- PHP database connection s...
- PHP operates the database...
- PHP database operation: m...
- PHP database operation: p...
- PHP database operation: b...
- PHP database operation to...
- The ultimate solution to ...
- Chapter14php session management and control
- session overview
- Overview of Cookies for P...
- php session control Cooki...
- PHP session control using...
- php SESSION application e...
- Session management and co...
- Chapter15Making a thief program through cURL
- php curl usage methods an...
- php curl custom get metho...
- php curl uses post to sen...
- Making a thief program th...
- Chapter16Learn commonly used English words in PHP
- List of commonly used Eng...