mysql学习笔记

1、设置完ft_min_word_len后，只重启mysql是不够的，还要重建fulltext。（For the setting to take effect, you will have to restart the MySQL server and regenerate the full-text index (the easiest way to do this is with REPAIR TABLE tablename QUICK).

 

2、隔离级别

 

隔离级别（isolation level）

 

l         隔离级别定义了事务与事务之间的隔离程度。

l         隔离级别与并发性是互为矛盾的：隔离程度越高，数据库的并发性越差；隔离程度越低，数据库的并发性越好。

l         ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别：

l          未提交读（read uncommitted）

l          提交读（read committed）

l          重复读（repeatable read）

l          序列化（serializable）

l         通过一些现象，可以反映出隔离级别的效果。这些现象有：

l          更新丢失（lost update）：当系统允许两个事务同时更新同一数据是，发生更新丢失。

l          脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

l          非重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。(A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.  )

l          幻像（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻像读。(A transaction reexecutes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.  )

l         下面是隔离级别及其对应的可能出现或不可能出现的现象

	Dirty Read	NonRepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

 

3 、用UPDATE user SET password = PASSWORD('secret') WHERE user = 'root'改完密码后要重启数据库或者执行命令FLUSH PRIVILEGES之后，新密码才能生效。

 

4、select ... lock in share mode  &  select ... for update

SELECT … LOCK IN SHARE MODE
A peculiarity of the InnoDB table driver is that SELECT commands are immediately executed even
on blocked records. The results returned do not consider the possibility of open transactions of
other clients, and thus return potentially outmoded data.
If you execute the SELECT with the key word LOCK IN SHARE MODE appended, then when the
command is executed, it is held pending until all transactions already begun have been terminated
(of course, only to the extent that these transactions affect result records of the SELECT command).
If SELECT … LOCK IN SHARE MODE is executed within a transaction, then additionally, all
result records for all other clients will be locked until the end of the transaction. Such a lock is called
a shared lock, whence the key word SHARE. With a shared lock you are assured that the records read
during your transactions are not being changed or deleted by other clients.
With a shared lock, locked records can continue to be read by all clients, even if other clients
are also using SELECT LOCK IN SHARE MODE. Any attempt by a client to change such records leads
to the client being blocked until your transaction is completed.

(上面的英文主要两层意思：1，当已经有transaction用到你的select查询到的结果的数据时，你的select...lock in share mode要等到别人的transaction执行完之后才会执行；2，当你的select...lock in share mode在执行时，你给数据上了一把共享锁，别人就只能读了。)
SELECT … FOR UPDATE
The key words FOR UPDATE also represent an extension of the normal SELECT. With this, all result
records are provided an exclusive lock.
With a shared lock, locked records cannot be changed by other clients. They can continue to be
read by all clients with a normal SELECT command, but not with SELECT … LOCK IN SHARE MODE.
The difference between a shared lock and an exclusive lock therefore relates only to whether other
clients can execute SELECT … LOCK IN SHARE MODE.(排它锁，当你的select...for update在执行时，别人只能等，别人连select...lock in  share mode 都不能使用。即不能读)