Summary of some MySQL database surface questions

What security should 1.sql statements consider?

1. Prevent SQL injection, escape special characters, filter or use a precompiled SQL statement to bind variables.

2. The principle of least privilege, especially do not use the root account, for different types of actions or the formation of different accounts.

3. Do not display the error information returned by the database to the user in the case of an error in SQL operation to prevent leaking server and database related information.

2. Briefly describe the differences in MySQL, index, primary key, unique index, federated Index, and how the performance of the database is affected.

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet.

The only task for a normal index (an index defined by the keyword key or index) is to speed up access to the data.

Ordinary indexes allow indexed data columns to contain duplicate values, and if you can determine that a data column contains only values that differ from each other, you should use the keyword UNIQE to define it as a unique index when you create an index on that data index. Therefore, unique indexes guarantee the uniqueness of data records.

A primary key, a special unique index, in which only one primary key index can be defined in a table, which is gradually used to uniquely identify a record and is created with the keyword primary key.

Indexes can overwrite multiple data columns, such as index indexes, which are federated indexes.

Indexes can greatly improve the query speed of the data, but it reduces the speed of inserting and deleting the update tables because the index files are also manipulated when performing these writes.

3. A table, which has an ID from the primary key, when the insert 17 records, delete the first 15,16,17 record, then restart the MySQL, and then insert a record, the record ID is 18 or 15?

If the type of the table is MyISAM, then it is 18.
Because the MyISAM table will record the maximum ID of the self-increment primary key into the data file, the maximum ID of the restart MySQL self-increment primary key will not be lost.
If the type of the table is InnoDB, then it is 15.
The InnoDB table simply logs the maximum ID of the self-increment primary key into memory, so restarting the database or optimize the table will cause the maximum ID to be lost.

4. Describe the ways in which you optimize the efficiency of SQL statement execution in your project. How is SQL statement performance analyzed?

1. Try selecting a smaller column

2. Index the more frequent fields in where

Avoid using ' * ' in 3.select clauses

4. Avoid using calculations on indexed columns, not,in and <> operations

5. Use limit 1 when only one row of data is needed

6. Ensure that the form data is not more than 200w, timely split the table

For queries that are slow, you can use explain to analyze the specific execution of the statement

The difference between 5.mysql_fetch_row () and mysql_fetch_array ()

Both functions return an array, the difference is that the first function returns an array that contains only the values, we can only $row[0], $row [1], so that the array subscript to read the data, and Mysql_fetch_array () The returned array contains both the first and the form of the key-value pairs , we can read the data in this way (if the database field is USERNAME,PASSWD): $row [' username '], $row [' passwd '.

Summary of some MySQL database surface questions