MySQL 5第十二次学科练习

这是第十二次ㄚ琪自我练习的结果,测试50题,答对46题得92分,错4题扣4分,总计得分88分,这应该还是有A等级,这几次的测试都有在A,已经让ㄚ琪的信心大增,并着手开始归划下一个项目的认证,考量到公家机关、国营事业及深入浅出的书籍搭配,ㄚ琪又再度拿起以前擅长的C了,又是一段时间没有磨练了,生锈了不少。好了,闲话太多了,回到主题吧,这一回错的题目就比较是没见过的了,让我们研究研究吧。

[adsense][/adsense]

2013-07-17_084943

这是MySQL 5实力养成暨评量里的4-11.‘整数型数值型态中的BIGINT需占多少Bytes的空间?’

答案:(D)8

这一提的症结点在于我们实在记不起来各种资料型态的范围,我们已经在MySQL 数值型态列过了这些资讯,无奈我们记不起来。我想我们应该更有效绿的去记吧,像是1 byte是TINYINT、SMALLINT就是再double、INT是double的double,而BIGINT就是INT的double了,这样或许会比较好记。

2013-07-17_085014

这是MySQL 5实力养成暨评量里的8-53.‘下列何项指令可解除锁定资料表emp?’

答案:(C)UNLOCK TABLES

这一题跟交易有关,不过我们没有很多经验,正如今天有个新来的总务,把很多事给搞砸了一样,马上就被冠上天字头的称号,不过这种事应该多练习就好了吧,我们来看看手册练习一下吧,MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.3 MySQL Transactional and Locking Statements :: 13.3.5 LOCK TABLES and UNLOCK TABLES Syntax 或 MySQL 5.1参考手册 :: 13. SQL语句语法::13.4. MySQL事务处理和锁定语句::13.4.5. LOCK TABLES和UNLOCK TABLES语法

转译繁体中文列于下:

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES可以锁定用于当前线程的资料表。如果资料表被其它线程锁定,则造成堵塞,直到可以获取所有锁定为止。UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时,或当与伺服器的连接被关闭时,所有由当前线程锁定的资料表被隐含地解锁。

资料表锁定只用于防止其它客户端进行不正当地读取和写入。保持锁定(即使是读取锁定)的客户端可以进行资料表层级的操作,比如DROP TABLE。

注意,下面是对交易资料表使用LOCK TABLES的说明:

·         在尝试锁定资料表之前,LOCK TABLES不是交易安全型的,会隐含地提交所有活性交易。同时,开始一项交易(例如,使用START TRANSACTION),会隐含地执行UNLOCK TABLES。(见13.4.3节,“会造成隐式提交的语句”

·         对交易资料表(如InnoDB)使用LOCK TABLES的正确方法是,设置AUTOCOMMIT=0并且不能使用UNLOCK TABLES,直到您明确地提交交易为止。当您使用LOCK TABLES时,InnoDB会内部地取其自己的资料表锁定,MySQL取其自己的资料表锁定。InnoDB在下一个提交时释放其资料表锁定,但是,对于MySQL,要释放资料表锁定,您必须使用UNLOCK TABLES。您不应该让AUTOCOMMIT=1,因为那样的话,InnoDB会在使用LOCK TABLES之后立刻释放资料表锁定,并且很容易形成死锁定。注意,如果AUTOCOMMIT=1,我们根本不能获取InnoDB资料表锁定,这样就可以帮助旧的应用软件避免不必要的死锁定。

·         ROLLBACK不会释放MySQL的非交易资料表锁定。

要使用LOCK TABLES,您必须拥有相关资料表的LOCK TABLES权限和SELECT权限。

使用LOCK TABLES的主要原因是仿效交易,或在更新资料表时加快速度。这将在后面进行更详细的解释。

如果一个线程获得对一个资料表地READ锁定,该线程(和所有其它线程)只能从该资料表中读取。如果一个线程获得对一个资料表的WRITE锁定,只有保持锁定的线程可以对资料表进行写入。其它的线程被阻止,直到锁定被释放时为止。

READ LOCAL和READ之间的区别是,READ LOCAL允许在锁定被保持时,执行非冲突性INSERT语句(同时插入)。但是,如果您正打算在MySQL外面操作资料库档案,同时您保持锁定,则不能使用READ LOCAL。对于InnoDB资料表,READ LOCAL与READ相同。

当您使用LOCK TABLES时,您必须锁定您打算在查询中使用的所有的资料表。虽然使用LOCK TABLES语句获得的锁定仍然有效,但是您不能访问没有被此语句锁定的任何的资料表。同时,您不能在一次查询中多次使用一个已锁定的资料表——使用别名代替,在此情况下,您必须分别获得对每个别名的锁定。

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

如果您的查询使用一个别名引用一个资料表,那么您必须使用同样的别名锁定该资料表。如果没有指定别名,则不会锁定该资料表。

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反的,如果您使用一个别名锁定一个资料表,您必须使用该别名在您的查询中引用该资料表。

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

WRITE锁定通常比READ锁定拥有更高的优先权,以确保更新被尽快地处理。这意味着,如果一个线程获得了一个READ锁定,则另一个线程会申请一个WRITE锁定,后续的READ锁定申请会等待,直到WRITE线程获得锁定并释放锁定。您可以使用LOW_PRIORITY WRITE锁定来允许其它线程在该线程正在等待WRITE锁定时获得READ锁定。只有当您确定最终将有一个时机,此时没有线程拥有READ锁定时,您才应该使用LOW_PRIORITY WRITE锁定。

LOCK TABLES按照如下方式执行:

1.    按照内部定义的顺序,对所有要被锁定的资料表进行分类。从用户的角度,此顺序是未经定义的。

2.    如果使用一个读取和一个写入锁定对一个资料表进行锁定,则把写入锁定放在读取锁定之前。

3.    一次锁定一个资料表,直到线程得到所有锁定为止。

该规则确保资料表锁定不会出现死锁定。但是,对于该规则,您需要注意其它的事情:

如果您正在对一个资料表使用一个LOW_PRIORITY WRITE锁定,这只意味着,MySQL等待特定的锁定,直到没有申请READ锁定的线程时为止。当线程已经获得WRITE锁定,并正在等待得到锁定资料表清单中的用于下一个资料表的锁定时,所有其它线程会等待WRITE锁定被释放。如果这成为对于应用程式的严重的问题,则您应该考虑把部分资料表转化为交易安全型资料表。

您可以安全地使用KILL来结束一个正在等待资料表锁定的线程。请参见13.5.5.3节,“KILL语法”

注意,您不能使用INSERT DELAYED锁定任何您正在使用的资料表,因为,在这种情况下,INSERT由另一个线程执行。

通常,您不需要锁定资料表,因为所有的单个UPDATE语句都是原子性的;没有其它的线程可以干扰任何其它当前正在执行的SQL语句。但是,在几种情况下,锁定资料表会有好处:

·         如果您正在对一组MyISAM资料表运行许多操作,锁定您正在使用的资料表,可以快很多。锁定MyISAM资料表可以加快插入、更新或删除的速度。不利方面是,没有线程可以更新一个用READ锁定的资料表(包括保持锁定的资料表),也没有线程可以访问用WRITE锁定的资料表(除了保持锁定的资料表以外)。

有些MyISAM操作在LOCK TABLES之下更快的原因是,MySQL不会清空用于已锁定资料表的关键缓存,直到UNLOCK TABLE被使用为止。通常,关键缓存在每个SQL语句之后被清空。

·         如果您正在使用MySQL中的一个不支援交易的储存引擎,则如果您想要确定在SELECT和UPDATE之间没有其它线程,您必须使用LOCK TABLES。本处所示的例子要求LOCK TABLES,以便安全地执行:

·                mysql> LOCK TABLES trans READ, customer WRITE;
·                mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
·                mysql> UPDATE customer
·                    ->     SET total_value=sum_from_previous_statement
·                    ->     WHERE customer_id=some_id;
·                mysql> UNLOCK TABLES;

如果没有LOCK TABLES,有可能另一个线程会在执行SELECT和UPDATE语句之间在trans资料表中插入一个新行。

通过使用相对更新(UPDATE customer SET value=value+new_value)或LAST_INSERT_ID()函数,您可以在许多情况下避免使用LOCK TABLES。请参见1.8.5.3节,“事务和原子操作”

通过使用用户层级的顾问式锁定函数GET_LOCK()和RELEASE_LOCK(),您也可以在有些情况下避免锁定资料表。这些锁定被保存在伺服器中的一个混编资料表中,使用pthread_mutex_lock() 和pthread_mutex_unlock(),以加快速度。请参见12.9.4节,“其他函数”

要了解更多有关锁定规则的说明,请参见7.3.1节,“锁定方法”

您可以使用FLUSH TABLES WITH READ LOCK语句锁定位于所有带有读取锁定的资料库中的所有资料表。请参见13.5.5.2节,“FLUSH语法”。如果您有一个可以及时拍摄快照的档案系统,比如Veritas,这是获得备份的一个非常方便的方式。

注释:如果您对一个已锁定的资料表使用ALTER TABLE,该资料表可能会解锁。请参见A.7.1节,“与ALTER TABLE有关的问题”

基本上没事不会去LOCK有的没的,但是一旦要解锁,就通通UNLOCK吧。

2013-07-17_085056

这是MySQL 5实力养成暨评量里的8-29.‘请参阅附图作答

自订函式如下:

CREATE FUNCTION func1() RETURNS INT

BEGIN

DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

ST v1 = v1 -1;

END WHILE;

RETURN v1;

END;

如使用者执行select func1();请问所得数值为何?

答案:(C) 0

这一题再回来看时,答案真的是0,但是为何就是会选1呢?真是诡异,所以我们不另立一篇来讨论。

2013-07-17_085142

这是MySQL 5实力养成暨评量里的4-63.‘在一个表格中,某些栏位的值是不能重复的,例如:帐号,通常我们会把这种栏位设成下列何项?’

答案:(C) UNIQUE

这一题似乎也要好好讨论一下,但是仔细一看又似乎是不小心答错的,所以我们不讨论了,只是要好好检讨不要太粗心。