MySQL Interview questions 50 Avenue , Highlights !
One ,Mysql What kind of locks are there ?
1. Table level lock ： Low cost , Lock fast ; There will be no deadlock ; Large locking granularity , The probability of lock conflict is the highest , The degree of concurrency is the lowest .
2. Row level lock ： It's expensive , Slow lock ; There will be a deadlock ; Minimum locking granularity , The probability of lock conflict is the lowest , The degree of concurrency is also the highest .
3. Page lock ： The cost and locking time are between table lock and row lock ; There will be a deadlock ; Locking granularity is between table lock and row lock , The degree of concurrency is average .
Two ,Mysql What are the different tables in ?
share 5 Types of tables ：
* MyISAM
* Heap
* Merge
* INNODB
* ISAM
Three , Brief description in MySQL In the database MyISAM and InnoDB The difference between ?
MyISAM：
Transaction is not supported , But every query is atomic ;
Table level lock support , That is, each operation is to lock the entire table ;
The total number of rows in the storage table ;
One MYISAM The table has three files ： Index file , Structure table , data file ;
Using phenanthrene clustering index , The data field of the index file stores the pointer to the data file . The secondary index is basically consistent with the primary index , But the secondary index is not guaranteed to be unique .
InnoDb：
support ACID Business of , Supports four isolation levels for transactions ;
Support row level lock and foreign key constraint ： Therefore, write concurrency can be supported ;
Do not store total rows ;
One InnoDb The engine is stored in a file space （ Shared table space , Table size is not controlled by the operating system , A table may be distributed in multiple files ）, It may also be more than one （ Set to independent table null , Table size is limited by operating system file size , It is generally 2G）, Limited by operating system file size ;
The primary key index is clustered index （ The indexed data field stores the data file itself ）, The data field of the secondary index stores the value of the primary key ; So look up the data from the secondary index , You need to find the primary key value through the secondary index , Access secondary index again ; It is better to use auto increment primary key , Prevent data insertion , To maintain B+ Tree structure , Big adjustment of documents .
Four ,Mysql in InnoDB Four transaction isolation level names supported , And the difference between levels ?
SQL The four isolation levels defined by the standard are ：
* read uncommited ： Uncommitted data read
* read committed： Dirty reading , Non repeatable
* repeatable read： Rereadable
* serializable ： Serial things
Five ,CHAR and VARCHAR The difference between ?
1.CHAR and VARCHAR Types differ in storage and retrieval
2.CHAR The column length is fixed to the length declared when the table was created , The length value range is 1 reach 255
When CHAR When the value is stored , They are filled with spaces to a specific length , retrieval CHAR Remove trailing spaces when value .
Six , The difference between primary key and candidate key ?
Each row of the table is uniquely identified by the primary key , A table has only one primary key .
The primary key is also a candidate key . By convention , Candidate keys can be specified as primary keys , And can be used for any foreign key reference .
Seven ,myisamchk What is it for ?
It's used for compression MyISAM surface , This reduces disk or memory usage .
MyISAM Static and MyISAM Dynamic What's the difference? ?
stay MyISAM Static All fields on have a fixed width . dynamic MyISAM The table will have an image TEXT,BLOB Etc , To adapt to data types of different lengths .
MyISAM Static Easier to recover in case of damage .
Eight , If there is a table, a column is defined as TIMESTAMP, What will happen ?
Whenever a row is changed , The timestamp field gets the current timestamp .
Column set to AUTO INCREMENT Time , If the maximum value is reached in the table , What will happen ?
It stops increasing , Any further insertion will result in an error , Because the key is already in use .
How can I find out which auto increment was allocated on the last insert ?
LAST_INSERT_ID Will be returned by Auto_increment Last value assigned , And you don't need to specify a table name .
Nine , How do you see all the indexes defined for the table ?
Indexes are defined for tables in the following ways ：
SHOW INDEX FROM
<tablename>;
Ten ,LIKE In the statement % and _ What do you mean? ?
％ Corresponding to 0 Characters or more ,_ just LIKE A character in a statement .
How to Unix and Mysql Conversion between timestamps ?
UNIX_TIMESTAMP It's from Mysql Timestamp to Unix Timestamp command
FROM_UNIXTIME It's from Unix Timestamp to Mysql Timestamp command
eleven , What is the column comparison operator ?
stay SELECT Statement is used in column comparison =,<>,<=,<,> =,>,<<,>>,<=>,AND,OR or LIKE operator .
twelve ,BLOB and TEXT What's the difference? ?
BLOB Is a binary object , It can hold a variable amount of data .TEXT It is case insensitive BLOB.
BLOB and TEXT The only difference between types is that BLOB Values are case sensitive when sorting and comparing , Yes TEXT Values are not case sensitive .
thirteen ,mysql_fetch_array and mysql_fetch_object What's the difference ?
Here are mysql_fetch_array and mysql_fetch_object The difference between ：
mysql_fetch_array（） – Returns the result row as an associative array or a regular array from the database .
mysql_fetch_object – Returns the result row from the database as an object .
fourteen ,MyISAM Where will the form be stored , It also provides its storage format ?
each MyISAM Tables are stored on disk in three formats ：
·“.frm” File storage table definition
· The data file has “.MYD”（MYData） Extension
The index file has “.MYI”（MYIndex） Extension
fifteen ,Mysql How to optimize DISTINCT?
DISTINCT Convert to on all columns GROUP BY, And with ORDER BY Clause combination .
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
sixteen , How to display the front 50 That's ok ?
stay Mysql in , Use the following code to query before display 50 That's ok ：
SELECT*FROMLIMIT 0,50;
seventeen , How many columns can be used to create an index ?
Any standard table can be created at most 16 Index columns .
eighteen ,NOW（） and CURRENT_DATE（） What's the difference? ?
NOW（） The command displays the current year , month , date , hour , Minutes and seconds .
CURRENT_DATE（） Show current year only , Month and date .
Technology