Whether it is operation and maintenance, development, testing, or architect, database technology is a must-have artifact. So, what do you want to learn about database and MySQL?
1. How to choose the type of server?
The meaning of each parameter in the MySQL server configuration window is as follows.
[Server Configuration Type] This option is used to set the type of server. Click the down button on the right side of the option, you can see 3 options.
The specific meanings of the 3 options are as follows:
Development Machine: This option represents a typical personal desktop workstation. Suppose there are multiple desktop applications running on the machine. Configure the MySQL server to use the least system resources.
Server Machine: This option represents the server. The MySQL server can run with other applications, such as FTP, email and web servers. The MySQL server is configured to use an appropriate proportion of system resources.
Dedicated
MySQL Server Machine (dedicated MySQL server): This option represents a server that only runs MySQL service. It is assumed that no other applications are running. The MySQL server is configured to use all available system resources. As a beginner, it is recommended to select the [DevelopmentMachine] option, which will occupy less system resources.
2. How to choose a
storage engine
Different storage engines have their own characteristics to meet different needs, as shown in the following table. To make a choice:
First, we need to consider what different functions each storage engine provides. If you want to provide transaction security (ACID compatible) capabilities for commit, rollback, and crash recovery capabilities, and require concurrency control, InnoDB is a good choice. If the data table is mainly used for inserting and querying records, the MyISAM engine can provide higher processing efficiency; if the data is only temporarily stored, the amount of data is not large, and high data security is not required, you can choose to save the data in memory The Memory engine in MySQL uses this engine as a temporary table to store intermediate results of queries. If there are only INSERT and SELECT operations, you can choose the Archive engine. The Archive storage engine supports highly concurrent insert operations, but it is not transaction safe. The Archive storage engine is very suitable for storing archived data. For example, you can use the Archive engine to record log information.
Which engine to use should be flexibly selected according to needs. Multiple tables in a database can use different engines to meet various performance and actual needs.
Using a suitable storage engine will improve the performance of the entire database.
3. How to check the default storage engine?
Use SHOW ENGINES statement to view all storage engines in the system, including the default storage engine. It can be seen that there are five storage engines in the current database system, and the default is MyISAM. You can also use a direct method to view the default storage engine. The execution result directly shows that the current default storage engine is MyISAM.
4. Be careful when deleting tables
The table delete operation will delete the definition of the table and the data in the table together, and MySQL will not prompt for any confirmation when executing the delete operation. Therefore, you should be careful when executing the delete operation. Before deleting the table, it is best to back up the data in the table so that when an operation error occurs, the data can be restored to avoid irreversible consequences. Similarly, when using ALTER TABLE to perform basic table modification operations, you should ensure that you have a complete backup of the data before performing the operation process, because the database changes cannot be undone. If you add an unnecessary field, you can Delete it; in the same way, if you delete a required column, all data under that column will be lost.
5. Is there a primary key in every table?
Not every table needs a primary key. Generally, if you connect multiple tables, you need to use the primary key. Therefore, it is not necessary to establish a primary key for each table, and in some cases it is better not to use the primary key.
6. Can the storage engine be chosen arbitrarily for each table?
Foreign key constraints (FOREIGN KEY) cannot be used across engines. MySQL supports multiple storage engines, each table can specify a different storage engine, but it should be noted: foreign key constraints are used to ensure the referential integrity of the data, if the table needs to be associated with foreign keys, but specify a different For storage engines, foreign key constraints cannot be created between these tables. Therefore, the choice of storage engine is not entirely arbitrary.
7. Does the field value with AUTO_INCREMENT constraint start from 1?
By default, in MySQL, the initial value of AUTO_INCREMENT is 1. Every time a new record is added, the field value is automatically increased by 1. When setting the auto-increment attribute (AUTO_INCREMENT), you can also specify the value of the auto-increment field of the first inserted record, so that the auto-increment field value of the newly inserted record increases from the initial value, such as inserting the first record in tb_emp8 , And specify the id value of 5 at the same time, then the id value of the inserted record will increase from 6 up. When adding unique primary key constraints, it is often necessary to set fields to automatically add attributes.
8. The difference between TIMESTAMP and DATATIME
TIMESTAMP and DATETIME have different storage bytes and supported ranges, but the biggest difference is: When DATETIME stores date data, it is stored in the format actually entered, that is, what is entered is stored, regardless of time zone; and TIMESTAMP value The storage of is saved in UTC (Universal Standard Time) format, the current time zone is converted when storing, and then converted back to the current time zone when searching. That is, when querying, the displayed time value is different according to the current time zone.
9. What are the methods and techniques for selecting data types?
MySQL provides a large number of data types. In order to optimize storage and improve database performance, the most accurate type should be used in any case. That is, of all the types that can represent the value of the column, this type uses the least storage.
Integer and floating point
If the fractional part is not needed, use an integer to save the data; if it needs to represent the fractional part, use the floating-point type. For floating-point data columns, the stored value will be rounded to the decimal place defined by the column. For example, if the value of the column is in the range of 1-99999, if you use an integer, then MEDIUMINT UNSIGNED is the best type; if you need to store decimals, use the FLOAT type. Floating point types include FLOAT and DOUBLE types. The DOUBLE type has higher accuracy than the FLOAT type. Therefore, if a higher storage accuracy is required, the DOUBLE type should be selected.
Floating point and fixed point
The advantage of floating-point numbers FLOAT and DOUBLE over fixed-point DECIMAL is that with a certain length, floating-point numbers can represent a larger data range. However, because floating-point numbers are prone to errors, it is recommended to use DECIMAL for storage when the accuracy requirements are relatively high. DECIMAL is stored as a string in MySQL, and is used to define data that requires high accuracy such as currency. In data migration, float(M,D) is a non-standard SQL definition, and database migration may cause problems, so it is best not to use it in this way. The other two floating-point numbers are also prone to problems when performing subtraction and comparison operations, so you must be careful when performing calculations. If you are performing numerical comparisons, it is best to use the DECIMAL type.
Date and time type
MySQL has many data types for different kinds of dates and times, such as YEAR and TIME. If you only need to record the year, you can use the YEAR type; if you only need to record the time, you only need to use the TIME type. If you need to record the date and time at the same time, you can use the TIMESTAMP or DATETIME type. Since the value range of the TIMESTAMP column is smaller than the value range of DATETIME, it is better to use DATETIME for dates with a larger storage range. TIMESTAMP also has an attribute that DATETIME does not have. By default, when a record is inserted but the TIMESTAMP column value is not specified, MySQL will set the TIMESTAMP column to the current time. Therefore, it is convenient to use TIMESTAMP when you need to insert records and insert the current time. In addition, TIMESTAMP is more efficient than DATETIME in space.
Features and choices between CHAR and VARCHAR
The difference between CHAR and VARCHAR:
CHAR is a fixed-length character, VARCHAR is a variable-length character; CHAR will automatically delete the trailing spaces of the inserted data, and VARCHAR will not delete the trailing spaces.
CHAR is a fixed length, so its processing speed is faster than that of VARCHAR, but its disadvantage is that it wastes storage space. Therefore, the storage is not large, but the CHAR type can be used if the speed is required, and the VARCHAR type can be used instead.
The impact of the storage engine on the choice of CHAR and VARCHAR:
For the MyISAM storage engine: It is best to use fixed-length data columns instead of variable-length data columns. This can make the entire table static, so that data retrieval is faster, and space is exchanged for time.
For the InnoDB storage engine: use variable-length data columns, because the storage format of the InnoDB data table does not distinguish between fixed length and variable length, so using CHAR is not necessarily better than using VARCHAR, but because VARCHAR is stored according to the actual length, It saves space, so it is better for disk I/O and total data storage.
ENUM and SET
ENUM can only take a single value, and its data list is an enumerated collection. Its legal value list allows up to 65,535 members. Therefore, when you need to select one of multiple values, you can use ENUM. For example, the gender field is suitable to be defined as ENUM type, and only one value can be taken from "male" or "female" at a time. SET can take multiple values. Its legal value list allows up to 64 members. An empty string is also a legal SET value. When you need to take multiple values, it is appropriate to use the SET type. For example, to store a person's hobbies, it is best to use the SET type. The values of ENUM and SET appear in the form of strings, but internally, MySQL stores them in the form of numbers.
BLOB and TEXT
BLOB is a binary string, and TEXT is a non-binary string, both of which can store large-capacity information. BLOB mainly stores pictures and audio information, while TEXT can only store plain text files. The purpose of the two should be distinguished.
10. How to use special characters in MySQL?
Symbols such as single quotation mark ('), double quotation mark ("), backslash (), etc., these symbols cannot be directly input and used in MySQL, otherwise it will produce unexpected results. In MySQL, these special characters are called conversion When inputting, you need to start with a backslash ('\'), so you should input (\') or (\") when you use single and double quotation marks, and you should input (\ ), other special characters include carriage return (\r), line feed (\n), tab (\tab), backspace (\b), etc. When inserting these special characters into the database, they must be escaped.
