In addition to the basic questions, the MySQL interview questions that Xiaobian plans to collect include the following knowledge points or questions: MySQL high performance index SQL statement MySQL query optimization MySQL is highly scalable and highly available MySQL security Let's start with a real question: Zhenti Please write down the meaning of the following MySQL data type expression (int(0), char(16), varchar(16), dateTIme, text) Knowledge analysis This Zhenti mainly examines the MySQL data type. The MySQL data type is based on the MySQL database. The extended knowledge points include the following: MySQL basic operations MySQL storage engine MySQL lock mechanism MySQL transaction processing, stored procedures, triggers Let’s go to the bottom of this knowledge Data type test sites: 1, the integer type, including TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, respectively, said 1 byte, 2 bytes, 3 bytes, 4 bytes, 8-byte integer. The UNSIGNED attribute can be added to any integer type to indicate that the data is unsigned, that is, a non-negative integer. Length: Integer type can be specified length, for example: INT(11) represents type INT of length 11. The length is meaningless in most scenes. It does not limit the legal range of the value. It only affects the number of displayed characters, and it only makes sense to use it with the UNSIGNED ZEROFILL attribute. For example, assume that the type is set to INT(5) and the attribute is UNSIGNED ZEROFILL. If the user inserts 12 data, the database actually stores data as 00012. 2, real number types, including FLOAT, DOUBLE, DECIMAL. DECIMAL can be used to store integers larger than BIGINT and store accurate decimals. FLOAT and DOUBLE have value ranges and support the use of standard floating-point approximations. FLOAT and DOUBLE are more efficient than DECIMAL in calculations. DECIMAL can be interpreted as a string. 3, the string type, including VARCHAR, CHAR, TEXT, BLOB VARCHAR is used to store variable-length strings, which is more space-efficient than fixed-length types. VARCHAR uses extra 1 or 2 bytes to store the length of the string. When the column length is less than 255 bytes, it is represented by 1 byte, otherwise it is represented by 2 bytes. When VARCHAR stores content that exceeds the set length, the content is truncated. CHAR is fixed-length, allocates enough space based on the defined string length. CHAR will be filled with spaces as needed to facilitate comparison. CHAR is suitable for storing very short strings, or all values ​​are close to the same length. When the content stored in CHAR exceeds the set length, the content is also truncated. Use strategy: For data that changes frequently, CHAR is better than VARCHAR because CHAR is less prone to fragmentation. For very short columns, CHAR is more efficient in storage than VARCHAR. When using it, pay attention to only allocate the space needed, and longer columns will consume more memory when sorting. Try to avoid using the TEXT/BLOB type. The query will use temporary tables, leading to serious performance overhead. 4. ENUM (ENUM) stores non-repetitive data as a predefined set. ENUM can sometimes be used instead of the usual string types. ENUM storage is very compact and compresses the list value to one or two bytes. When ENUM is stored internally, it is an integer. Try to avoid using numbers as constants for the ENUM enumeration because it is confusing. Sorting is based on internally stored integers 5, date and time types, try to use TImestamp, space efficiency is higher than dateTIme, It's usually inconvenient to save a timestamp with an integer. If you need to store subtlety, you can use bigint storage. Seeing this, this question is not easy to answer. Answer: int(0) indicates that the data is of type INT, the length is 0, char(16) represents a fixed-length string, the length is 16, varchar(16) represents a variable-length string, the length is 16, datetime represents a time type, Text represents a string type that can store a large string of up to 65535 bytes of data) MySQL basic operations: Common operations MySQL connection and shutdown: mysql -u -p -h -P -u: Specifies the user name -p: specify the password -h: host -P:port After entering the MySQL command line: G, c, q, s, h, d G: Vertical display of print results c: cancel the current MySQL command q: Exit MySQL connection s: shows the server status h: help information d: change the execution MySQL storage engine: 1, InnoDB storage engine, The default transactional engine, the most important and most extensive storage engine, has excellent performance. Data is stored in shared tablespaces and can be separated by configuration. That is, multiple tables and indexes are stored in a single tablespace. This configuration can be changed through the configuration file. The performance of primary key queries is higher than other types of storage engines. The internal has done a lot of optimizations, automatically building a hash index when reading data from disk, and automatically inserting buffers when inserting data. Supports true hot backup through some mechanisms and tools. Supports safe recovery after a crash. Row-level locking is supported. Support foreign keys. 2, MyISAM storage engine, Before 5.1, it was the default storage engine. Has full-text indexing, compression, and spatial functions. Does not support transactional and row-level locking, does not support crash recovery after the security. The table is stored in two files, MYD and MYI. The design is simple, and the performance is good in some scenarios. For example, how many pieces of data are obtained for the entire table, and the performance is very high. Full-text indexing is not very common and it is not as good as using external ElasticSearch or Lucene. 3, other table engines, Archive, Blackhole, CSV, Memory Use strategy The InnoDB storage engine is recommended for most scenarios. MySQL lock mechanism: Table lock is a common problem in daily development. Therefore, it is also the most common investigation point in the interview. When multiple queries are modified at the same time, the problem of concurrency control arises. Shared and exclusive locks are read and write locks. Shared locks, no blocking, multiple users can read a resource at the same time without disturbing each other. Exclusive lock, a write lock will block other read locks and write locks, so that only one user can be written to prevent other users from reading the resource being written. The granularity of the lock Table locks have minimal system overhead, lock the entire table, and MyIsam uses table locks. Row locks maximize support for concurrency processing, but also bring maximum lock overhead. InnoDB uses row locks. MySQL transaction processing: MySQL provides a transaction processing table engine, which is InnoDB. The server layer does not manage transactions, and is implemented by the underlying engine. Therefore, using multiple engines in the same transaction is not reliable. It should be noted that MySQL does not issue alerts and does not report errors when executing transaction operations on non-transactional tables. Stored procedure: A set of one or more MySQL statements saved for later use, so you can also add business logic and processes to your stored procedures. You can create tables, update data, delete data, and so on in a stored procedure. Use strategy Simplify complex operations by encapsulating SQL statements in easy-to-use units Can guarantee the consistency of the data Simplify management of changes trigger: A method of providing data integrity to programmers and data analysts is a special stored procedure associated with a table event. scenes to be used Cascading changes can be made through related tables in the database. Real-time monitoring of changes to a field in a table requires corresponding processing. For example, you can generate the number of some business. Be careful not to abuse it, otherwise it will cause difficulties in database and application maintenance. We need to keep in mind the above basic knowledge, the key is to understand the difference between the data types CHAR and VARCHAR, the difference between the table storage engine InnoDB and MyISAM. Question 1: Please explain the difference between InnoDB and MyISAM A: InnoDB supports transactions and MyISAM does not support them; InnoDB data is stored in shared tablespaces, and MyISAM data is stored in files; InnoDB supports row-level locking, MyISAM only supports table locks; InnoDB supports recovery after crash, MyISAM does not support; InnoDB supports foreign keys, which MyISAM does not support; InnoDB does not support full-text indexing, MyISAM supports full-text indexing; Question 2: The characteristics of the innodb engine A: insert buffer Double write Adaptive hash index (ahi) Read ahead Question 3: Please list more than 3 table engines Answer: InnoDB, MyISAM, Memory Question 4: Please explain the difference between varchar and text A: varchar can specify the number of characters, text can not be specified, the internal storage varchar is the actual number of characters stored +1 bytes (n "= 255) or 2 bytes (n "255), text is the actual number of characters + 2 bytes. The text type cannot have a default value. Varchar can be directly created index, text to create an index to specify the first few characters. The varchar query is faster than text. In the case of creating an index, the index of the text has almost no effect. Querying text requires creating a temporary table. Question 5: Meaning of 50 in varchar(50) A: Store up to 50 characters, varchar (50) and (200) store the same space as hello, but the latter will consume more memory when sorting, because order by col uses fixed_length to calculate col length (memory engine is the same) . Question 6: The meaning of 20 in int(20) Answer: refers to the length of the displayed character, does not affect the internal storage, but when defining the ZEROFILL, how many 0 PU Leather Flip Cover Shockproof Case PU Leather Flip Cover Shockproof Case,Pu Leather Case,Genuine Pu Leather Case,Best Premium Pu Leather Case Guangzhou Jiaqi International Trade Co., Ltd , https://www.make-case.com