PHP development basic tutorial types, character sets, engines and indexes

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, comply with the length requirements of the data type.

In MySQL we divide data types into the following types:

• Numeric types (integer, floating point)

• String type

• Date time type

• Composite type

Integer type

##The length of the integer type is different. The actual usage process is PHP Academy.

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.
Similarly 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

##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

##

*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

## 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 one value is allowed to be obtained from a collection; the SET type allows any number of values ​​to be obtained from a collection.

1.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.

2.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.
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

• default The default 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

null When specifying the null attribute for a column, 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.

---

Character set

What is the character set

In order to better recognize Chinese, Japanese, English, and Greek. Commonly used symbols are encoded, and this encoding is the character set.

The character set determines how text is stored.

The character set is equivalent to the human language in the computer.

For example:

I speak English, so when I store it, I need to use English text to store it.
If I am talking about Chinese, use English characters to store it. Then people can’t read or understand it, it’s what we call gibberish.

Because there are too many character sets, enough to have dozens or hundreds of them. So we don't need to know too much about character sets, or even how character sets are compiled into human-visible characters.

Key knowledge of character sets

We only need to understand:

• Commonly used Character set

• What character set do we use in the database

English character set:

ASCII

ASCII code uses a specified 7-bit or 8-bit binary number combination to represent 128 or 256 possible characters. Standard ASCII code, also called Basic ASCII code, uses 7-bit binary numbers to represent all uppercase and lowercase letters, numbers 0 to 9, punctuation marks, and special control characters used in American English.
Among them:
0~31 and 127 (33 in total) are control characters or special communication characters (the rest are displayable characters), such as control characters: LF (line feed), CR (carriage return), FF ( Page feed), DEL (delete), BS (backspace), BEL (ring), etc.; communication special characters: SOH (head of text), EOT (end of text), ACK (confirmation), etc.; ASCII values ​​are 8, 9 , 10 and 13 are converted to backspace, tab, line feed and carriage return characters respectively. They do not have a specific graphic display, but will have different effects on text display depending on the application.
32~126 (95 in total) are characters (32 is a space), of which 48~57 are ten Arabic numerals from 0 to 9.
65~90 are 26 uppercase English letters, 97~122 are 26 lowercase English letters, and the rest are some punctuation marks, arithmetic symbols, etc.

GBK

GBK is backward compatible with GB 2312 encoding. It is a Chinese character computer encoding specification defined by the People's Republic of China. The earlier version is GB2312.

Unicode

Unicode (Unicode, Universal Code, Unicode) Unicode is a character encoding scheme developed by an international organization that can accommodate all texts and symbols in the world. To meet the requirements of cross-language and cross-platform text conversion and processing.

UTF-8

is a variable-length character encoding for Unicode, and it is also a universal code. Because UNICODE takes up twice as much space as ASCII, and the high byte 0 is of no use to ASCII. In order to solve this problem, some intermediate format character sets have appeared. They are called universal conversion formats, that is, UTF (Universal Transformation Format)

Encoding to be used in actual work

The commonly used character sets in Chinese are divided into utf-8 and GBK.

The actual ones used are as follows:

Observe the characteristics of (Figure 1) and you will find that the MySQL character set consists of three parts:
1.Character set
2.Language
3.Type

Finally The bin refers to the binary character set, and the following ci refers to the case-insensitive characters when storing and sorting.

Note:
mysql writes utf8 when writing utf-8. Do not add the middle horizontal line.

---

TableEngine

The power of MySQL is With its plug-in storage engine, we can use different storage engines based on the characteristics of the table to achieve the best performance.

If you are familiar enough and have certain work experience. You can also use the MySQL engine open sourced by Alibaba and NetEase in your own server.

You can learn the statements for creating tables in the following section "Database Structure Definition Statements". When mysql creates a table, you can specify the corresponding engine.

Use in the mysql command:

show engines;

You can view all engines supported by the current server.

We introduce several commonly used engines and learn about several less commonly used engines. Avoid seeing some engines that don’t know the concepts in actual work in the future.

MyISAM

Does not support transactions, table locks (table-level locks, locking will lock the entire table), supports full-text indexes, and has fast operation speed. Often used for businesses that read a lot.

• Myisam storage engine table consists of myd and myi. .myd is used to store data files, and .myi is used to store index files.

• For myisam storage engine tables, the mysql database only caches its index files, and the caching of data files is completed by the operating system itself.

InnoDB

• supports transactions, mainly for online transaction processing (OLTP) applications.

• Row lock design supports foreign keys, that is, read operations are not locked by default.

InnoDB is designed for maximum performance when processing huge amounts of data.

Note:
Row lock: Lock this row during write and update operations to prevent others from operating it.
Table lock: During write and update operations, lock the table to prevent others from operating it.
Transaction: Operate multiple data at the same time, if one of the data operations fails. Can roll back to before the operation. Commonly used in banking, e-commerce, finance and other systems.

---

Index

Index looks like a very distinguished name, To put it bluntly, it is the latest table of contents of our book.

If you use Xinhua Dictionary to search for the Chinese character "张" without using the table of contents, you may have to go from the first page of Xinhua Dictionary to the last page, which may take two hours. The thicker the dictionary, the more time it will take you. Now you use the directory to search for the Chinese character "Zhang". The first letter of Zhang is z, and the Chinese characters starting with z start from more than 900 pages. With this clue, it may only take a minute for you to search for a Chinese character. This shows the importance of the index. sex.

Indexes are used to quickly find rows that have a specific value in a column.

Without using an index, MySQL must start from the first record and then read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the queried column in the table has an index, MySQL can quickly get to a point where it searches the middle of the data file without having to look at all the data.

Of course, it is not easy to have too many indexes. The more indexes are written, the slower the modification speed will be. Because when writing modified data, the index must also be modified.

MySQL index type