sql语句查询数据库中的表名/列名/主键/自动增长值----查询数据库中用户创建的表----jsj01 为数据库名select name tablename from jsj01..sysobjects where type='U' and name not in ('dtproperties')--查询表里的字段信息---docs为表名---- select * from syscolumns where id = object_id('docs')----查询数据库中所有类型----select name,xtype from systypes----两表联查，显示表中所有字段和对应的数据类型----syscolumns里字段‘xtype' 对应 systypes里的 ‘xusertype' ,systypes 里的‘name'字段就是字段的数据类型----docs 为表名select a.name as fieldname,b.name as type from syscolumns as a join systypes as b on a.xtype = b.xusertype where id=object_id('docs')----docs为数据表名 ： 查询表字段、类型、说明select a.name fieldname,b.name type,c.value comment from syscolumns as a full join systypes as b on a.xtype = b.xusertype full join ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', 'docs', 'column', default) as c ----这是2000版本，2005 把user改为schemaon a.name=c.objname COLLATE Chinese_PRC_CI_AS -----排序规则（有时不加也可以，如果两表的排序规则不同，则会报错）--join sysproperties c --on a.id=c.major_id where id=object_id('docs')----查询表里的主键，没有主键为空，如果是多个组合主键就有多个值 pk为主键 fk为外键--- jsj01 为数据库名 docs为表名 fk表示外键select column_name as primarykey,* from [jsj01].INFORMATION_SCHEMA.KEY_COLUMN_USAGE where Table_name='docs' and constraint_name like 'fk_%'--select * from sysobjects WHERE OBJECT_NAME(sysobjects.parent_obj)='docs' --and xtype='pk'--select * from sysconstraints where id = object_id('docs')--select * from syscolumns where id = object_id('docs')--select * from sysindexes--select * from sysindexkeys----查询表中自动增长的字段，没有为空，如果有就只有一个----docs为表名SELECT a.name column_name,b.name data_type FROM syscolumns a,systypes b WHERE a.id=object_id('docs') and a.xtype = b.xusertype AND a.autoval is not null

前言：前段时间新项目上线为了赶进度很多模块的功能都没有经过详细的测试导致了生成环境中的数据和实际数据对不上，因此需要自己手写一个数据库脚本来更新下之前的数据。（线上数据库用是SQL Server2012）关于数据统计汇总的问题肯定会用到遍历统计汇总，那么问题来了数据库中如何遍历呢？好像并没有for和foreach这种类型的功能呀，不过关于数据库遍历最常见的方法当然是大家经常会想到的游标啦，但是这次我并没有使用游标，而是通过创建临时表的方式来更新遍历数据的。为什么不使用游标，而使用创建临时表？首先使用游标的方式遍历数据可能代码上比较直观，但是代码比较繁琐（声明游标，打开游标，使用游标，关闭游标和释放游标）并且不符合操作集合的原则，而且也非常的耗费性能，因此通常数据量比较大的情况下不推荐使用游标。通过临时表while遍历数据，更符合我们日常的编程思想操作集合原则，性能上虽不敢保证表使用游标要好多少，但是在把临时表使用恰当的前提是能减少大量的性能消耗，并且使用起来非常简单易懂。通过创建临时表遍历更新数据：注意：这里只是一个简单的临时表更新实例。我的目的是把TalkingSkillType表中的Sort值更新成为与Id一样的值！未更新前的数据如下图所示：临时表遍历更新SQL语句：----SQL SERVER通过临时表遍历数据 -- 判断是否存在（object(‘objectname',‘type')） IF OBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL DROP TABLE dbo.#temp;   GO -- 声明变量  DECLARE  @ID AS INT,  @Name AS VARCHAR(50),  @Num AS INT   --数据插入临时表（select * INTO #Temp from 来源表） SELECT ID,Name INTO #temp FROM TalkingSkillType   --查询临时表中数据 --SELECT * FROM #temp    set @Num=0 --赋初始值   --查询是否存在记录，只要存在会一直循环直到不存在（WHILE EXISTS） WHILE EXISTS(SELECT ID FROM #temp)  BEGIN       set @Num= @Num + 1          -- 取值(把临时表中的值赋值给定义的变量)    SELECT top 1 @ID= ID,@Name=Name FROM #temp;       -- 输出操作（用于查看执行效果）    PRINT(@Num)        --更新    UPDATE TalkingSkillType SET Sort=@ID where id=@ID       -- 删除本次操临时表中的数据（避免无限循环）    DELETE FROM #temp WHERE ID=@ID;  END   --删除临时表 #temp --drop table #tempPRINT(@Num)输入日志：遍历更新成功后结果如下图所示：

MySQL 5.1对服务器一方的预制语句提供支持。如果您使用合适的客户端编程界面，则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势。候选界面包括MySQL C API客户端库（用于C程序）、MySQL Connector/J（用于Java程序）和MySQL Connector/NET。例如，C API可以提供一套能组成预制语句API的函数调用。其它语言界面可以对使用了二进制协议（通过在C客户端库中链接）的预制语句提供支持。对预制语句，还有一个SQL界面可以利用。与在整个预制语句API中使用二进制协议相比，本界面效率没有那么高，但是它不要求编程，因为在SQL层级，可以直接利用本界面：当您无法利用编程界面时，您可以使用本界面。有些程序允许您发送SQL语句到将被执行的服务器中，比如mysql客户端程序。您可以从这些程序中使用本界面。即使客户端正在使用旧版本的客户端库，您也可以使用本界面。唯一的要求是，您能够连接到一个支持预制语句SQL语法的服务器上。预制语句的SQL语法在以下情况下使用：在编代码前，您想要测试预制语句在您的应用程序中运行得如何。或者也许一个应用程序在执行预制语句时有问题，您想要确定问题是什么。 您想要创建一个测试案例，该案例描述了您使用预制语句时出现的问题，以便您编制程序错误报告。 您需要使用预制语句，但是您无法使用支持预制语句的编程API。预制语句的SQL语法基于三个SQL语句：PREPARE stmt_name FROM preparable_stmt;   EXECUTE stmt_name [USING @var_name [, @var_name] ...];   {DEALLOCATE | DROP} PREPARE stmt_name;PREPARE语句用于预备一个语句，并赋予它名称stmt_name，借此在以后引用该语句。语句名称对案例不敏感。preparable_stmt可以是一个文字字符串，也可以是一个包含了语句文本的用户变量。该文本必须展现一个单一的SQL语句，而不是多个语句。使用本语句，'?'字符可以被用于制作参数，以指示当您执行查询时，数据值在哪里与查询结合在一起。'?'字符不应加引号，即使您想要把它们与字符串值结合在一起，也不要加引号。参数制作符只能被用于数据值应该出现的地方，不用于SQL关键词和标识符等。如果带有此名称的预制语句已经存在，则在新的语言被预备以前，它会被隐含地解除分配。这意味着，如果新语句包含一个错误并且不能被预备，则会返回一个错误，并且不存在带有给定名称语句。预制语句的范围是客户端会话。在此会话内，语句被创建。其它客户端看不到它。在预备了一个语句后，您可使用一个EXECUTE语句（该语句引用了预制语句名称）来执行它。如果预制语句包含任何参数制造符，则您必须提供一个列举了用户变量（其中包含要与参数结合的值）的USING子句。参数值只能有用户变量提供，USING子句必须准确地指明用户变量。用户变量的数目与语句中的参数制造符的数量一样多。您可以多次执行一个给定的预制语句，在每次执行前，把不同的变量传递给它，或把变量设置为不同的值。要对一个预制语句解除分配，需使用DEALLOCATE PREPARE语句。尝试在解除分配后执行一个预制语句会导致错误。如果您终止了一个客户端会话，同时没有对以前已预制的语句解除分配，则服务器会自动解除分配。以下SQL语句可以被用在预制语句中：CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE和多数的SHOW语句。目前不支持其它语句。以下例子显示了预备一个语句的两种方法。该语句用于在给定了两个边的长度时，计算三角形的斜边。第一个例子显示如何通过使用文字字符串来创建一个预制语句，以提供语句的文本：mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ |     5 | +------------+ mysql> DEALLOCATE PREPARE stmt1;第二个例子是相似的，不同的是提供了语句的文本，作为一个用户变量：mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2 FROM @s; mysql> SET @a = 6; mysql> SET @b = 8; mysql> EXECUTE stmt2 USING @a, @b; +------------+ | hypotenuse | +------------+ |     10 | +------------+ mysql> DEALLOCATE PREPARE stmt2;对于已预备的语句，您可以使用位置保持符。以下语句将从tb1表中返回一行：mysql> SET @a=1; mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?"; mysql> EXECUTE STMT USING @a;以下语句将从tb1表中返回第二到第六行：mysql> SET @skip=1; SET @numrows=5; mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?"; mysql> EXECUTE STMT USING @skip, @numrows;预制语句的SQL语法不能被用于带嵌套的风格中。也就是说，被传递给PREPARE的语句本身不能是一个PREPARE, EXECUTE或DEALLOCATE PREPARE语句。预制语句的SQL语法与使用预制语句API调用不同。例如，您不能使用mysql_stmt_prepare() CAPI函数来预备一个PREPARE, EXECUTE或DEALLOCATE PREPARE语句。预制语句的SQL语法可以在已存储的过程中使用，但是不能在已存储的函数或触发程序中使用。以上就是本文的全部内容，希望对大家的学习有所帮助。

一、概述     变量在存储过程中会经常被使用，变量的使用方法是一个重要的知识点，特别是在定义条件这块比较重要。       mysql版本：5.6二、变量定义和赋值 #创建数据库 DROP DATABASE IF EXISTS Dpro; CREATE DATABASE Dpro CHARACTER SET utf8 ; USE Dpro; #创建部门表 DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (id INT NOT NULL PRIMARY KEY COMMENT '主键',  name VARCHAR(20) NOT NULL COMMENT '人名',  depid INT NOT NULL COMMENT '部门id' ); INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);declare定义变量在存储过程和函数中通过declare定义变量在BEGIN...END中，且在语句之前。并且可以通过重复定义多个变量注意：declare定义的变量名不能带‘@'符号，mysql在这点做的确实不够直观，往往变量名会被错成参数或者字段名。DECLARE var_name[,...] type [DEFAULT value]例如：DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE pname VARCHAR(20) DEFAULT '陈'; SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid; END$$ DELIMITER ;SET变量赋值SET除了可以给已经定义好的变量赋值外，还可以指定赋值并定义新变量，且SET定义的变量名可以带‘@'符号，SET语句的位置也是在BEGIN ....END之间的语句之前。1.变量赋值SET var_name = expr [, var_name = expr] ... DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE pname VARCHAR(20) DEFAULT '陈'; SET pname='王'; SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname; END$$ DELIMITER ; CALL Pro_Employee(101,@pcount);  SELECT @pcount;2.通过赋值定义变量DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE pname VARCHAR(20) DEFAULT '陈'; SET pname='王'; SET @ID=1; SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname; SELECT @ID; END$$ DELIMITER ; CALL Pro_Employee(101,@pcount);SELECT ... INTO语句赋值通过select into语句可以将值赋予变量，也可以之间将该值赋值存储过程的out参数,上面的存储过程select into就是之间将值赋予out参数。DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE pname VARCHAR(20) DEFAULT '陈'; DECLARE Pid INT; SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname; SELECT Pid; END$$ DELIMITER ; CALL Pro_Employee(101,@pcount);这个存储过程就是select into将值赋予变量；表中并没有depid=101 and name='陈'的记录。三、条件 条件的作用一般用在对指定条件的处理，比如我们遇到主键重复报错后该怎样处理。定义条件 定义条件就是事先定义某种错误状态或者sql状态的名称，然后就可以引用该条件名称开做条件处理，定义条件一般用的比较少，一般会直接放在条件处理里面DECLARE condition_name CONDITION FOR condition_value   condition_value:   SQLSTATE [VALUE] sqlstate_value  | mysql_error_code1.没有定义条件：DROP PROCEDURE IF EXISTS Pro_Employee_insert; DELIMITER $$ CREATE PROCEDURE Pro_Employee_insert() MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN SET @ID=1; INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100); SET @ID=2; INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100); SET @ID=3; END$$ DELIMITER ; #执行存储过程 CALL Pro_Employee_insert(); #查询变量值 SELECT @ID,@X;报主键重复的错误，其中1062是主键重复的错误代码，23000是sql错误状态2.定义处理条件DROP PROCEDURE IF EXISTS Pro_Employee_insert; DELIMITER $$ CREATE PROCEDURE Pro_Employee_insert() MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN #定义条件名称， DECLARE reprimary CONDITION FOR 1062; #引用前面定义的条件名称并做赋值处理 DECLARE EXIT HANDLER FOR reprimary SET @x=1; SET @ID=1; INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100); SET @ID=2; INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100); SET @ID=3; END$$ DELIMITER ; CALL Pro_Employee_insert(); SELECT @ID,@X;在执行存储过程的步骤中并没有报错，但是由于我定义的是exit，所以在遇到报错sql就终止往下执行了。接下来看看continue的不同。DROP PROCEDURE IF EXISTS Pro_Employee_insert; DELIMITER $$ CREATE PROCEDURE Pro_Employee_insert() MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN #定义条件名称， DECLARE reprimary CONDITION FOR SQLSTATE '23000'; #引用前面定义的条件名称并做赋值处理 DECLARE CONTINUE HANDLER FOR reprimary SET @x=1; SET @ID=1; INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100); SET @ID=2; INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100); SET @ID=3; END$$ DELIMITER ; CALL Pro_Employee_insert(); SELECT @ID,@X;其中红色标示的是和上面不同的地方，这里定义条件使用的是SQL状态，也是主键重复的状态；并且这里使用的是CONTINUE就是遇到错误继续往下执行。 条件处理条件处理就是之间定义语句的错误的处理，省去了前面定义条件名称的步骤。DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement   handler_type:   CONTINUE| EXIT| UNDO   condition_value:   SQLSTATE [VALUE] sqlstate_value  | condition_name  | SQLWARNING  | NOT FOUND  | SQLEXCEPTION  | mysql_error_codehandler_type：遇到错误是继续往下执行还是终止，目前UNDO还没用到。CONTINUE:继续往下执行EXIT：终止执行condition_values:错误状态SQLSTATE [VALUE] sqlstate_value：就是前面讲到的SQL错误状态，例如主键重复状态SQLSTATE '23000'condition_name：上面讲到的定义条件名称SQLWARNING：是对所有以01开头的SQLSTATE代码的速记，例如：DECLARE CONTINUE HANDLER FOR SQLWARNING。NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。mysql_error_code:是错误代码，例如主键重复的错误代码是1062，DECLARE CONTINUE HANDLER FOR 1062 语句：DROP PROCEDURE IF EXISTS Pro_Employee_insert; DELIMITER $$ CREATE PROCEDURE Pro_Employee_insert() MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN #引用前面定义的条件名称并做赋值处理 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2; #开始事务必须在DECLARE之后 START TRANSACTION ; SET @ID=1; INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100); SET @ID=2; INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100); SET @ID=3; IF @x=2 THEN  ROLLBACK; ELSE  COMMIT; END IF;  END$$ DELIMITER ; #执行存储过程 CALL Pro_Employee_insert(); #查询 SELECT @ID,@X;通过SELECT @ID,@X可以知道存储过程已经执行到了最后，但是因为存储过程后面有做回滚操作整个语句进行了回滚，所以ID=7的符合条件的记录也被回滚了。总结 变量的使用不仅仅只有这些，在光标中条件也是一个很好的功能，刚才测试的是continue如果使用EXIT的话语句执行完“SET @ID=2;”就不往下执行了，后面的IF也不被执行整个语句不会被回滚，但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长，在这里可以利用循环，当出现错误立刻退出循环执行后面的if回滚操作，在下一篇讲循环语句会写到，欢迎关注华为云社区数据库板块·。

如何快速的复制一张表首先创建一张表db1.t，并且插入1000行数据，同时创建一个相同结构的表db2.t假设，现在需要把db1.t里面的a>900的数据行导出来，插入到db2.t中mysqldump方法几个关键参数注释：–single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；–no-create-info的意思是，不需要导出表结构；–result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。导出csv文件select * from db1.t where a>900 into outfile '/server_tmp/t.csv';这条语句会将结果保存在服务端。如果你执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生成t.csv文件的。这条命令不会帮你覆盖文件，因此你需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。得到.csv导出文件后，你就可以用下面的load data命令将数据导入到目标表db2.t中。load data infile '/server_tmp/t.csv' into table db2.t;打开文件/server_tmp/t.csv，以制表符(\t)作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；启动事务。判断每一行的字段数与表db2.t是否相同：若不相同，则直接报错，事务回滚；若相同，则构造成一行，调用InnoDB引擎接口，写入到表中。重复步骤3，直到/server_tmp/t.csv整个文件读入完成，提交事务。物理拷贝方法mysqldump方法和导出CSV文件的方法，都是逻辑导数据的方法，也就是将数据从表db1.t中读出来，生成文本，然后再写入目标表db2.t中。有物理导数据的方法吗？比如，直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下，是否可行呢？答案是不行的。因为，一个InnoDB表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统是不会识别和接受它们的。在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。假设现在的目标是在db1的库下，复制一个跟表t相同的表r，具体执行步骤：执行create table r like t，创建一个相同表结构的空表，执行alter table r discard tablespace，这时候r.ibd文件会被删除执行flush table t for export这时候会生成一个t.cfg在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；执行unlock tables，这时候t.cfg文件会被删除；执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。这三种方法的优缺点物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。但必须是全拷贝，不能是部分拷贝，需要到服务器上拷贝数据，在用户无法登录数据库主机时无法使用，而且源表和目标表都必须是innodb引擎。用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。用select … into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。后两种都是逻辑备份方式，可以跨引擎使用的。mysql全局权限SELECT * FROM MYSQL.USER WHERE USER='UA'\G 显示所有权限作用域整个mysql，信息保存在mysql的user表里赋予用户ua一个最高权限：grant all privileges on *.* to 'ua'@'%' with grant option;同样也是相对应的两个操作，磁盘中权限字段修改位N，内存中对象的access的值修改位0。mysqlDB权限grant all privileges on db1.* to 'ua'@'%' with grant option;使用SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G来查看当前用户的db权限，同样的也是对磁盘和内存中的对象修改权限db权限存储在mysql.db表中注意：和全局权限不同，db权限会对已经存在的连接对象产生影响。mysql表权限和列权限表权限放在mysql.tables_priv中，列权限存放在mysql.columns_priv中，这两类权限组合起来存放在内存的hash结构column_priv_hash中。跟db权限类似，这两个权限每次grant的时候都会修改数据表，也会同步修改内存中的hash结构，因此，这两类权限的操作，也会影响到已经存在的连接。flush privileges的使用场景有些文档里提到，grant之后马上执行flush privileges命令，才能使赋权语句生效。其实更准确的说法应该是在数据表中的权限跟内存中的权限数据不一致的时候，flush privileges语句可以用来重建内存数据，达到一致状态。比如某时刻删除了数据表的记录，但是内存的数据还存在，导致了给用户赋权失败，因为在数据表中找不到记录。同时重新创建这个用户也不行，因为在内存判断的时候，会认为这个用户还存在。以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

作为精致美丽的都市丽人，拍照修图已成为人手必备的专业技能，“变美”成为人们不可或缺的追求。为了帮助用户变美，美图公司携手华为云构建了一套安全可用、稳定可靠的数据库服务，实现了性能的极大提升和业务的持续稳定，以技术赋能用户“变美”。美图作为中国领先的影像处理及社交平台，以变美为基因开发了美图秀秀、美颜相机、美拍、美图魔镜、美图手机等一系列产品，目前已经覆盖100个国家和地区，在全球拥有20.8亿用户，月均活跃用户超过2.95亿，2020年上半年营收入同比增长20.1%。美图每月因美而生超过60亿照片和视频，海量用户高并发的访问对业务性能、可靠性、资源快速部署等都提出了极高的要求。基于对数据库高性能的迫切需求，美图公司与华为启动战略合作，通过RDS proxy + RDS分布式HA系统 + DRS容灾的专属组合解决方案，完成了7大业务模块平滑顺利迁移至华为公有云，实现了数据库性能的极大提升和稳定运行。一键读写分离+ RDS 分布式HA系统，数据库上云更稳定可靠美图业务场景主要是互联网业务，对数据库稳定性要求极高。针对这个问题，华为云使用RDS高可靠分布式HA系统和PROXY多只读从库负载均衡，数据库稳定性得到增强；同时支持可靠性和可用性优先两种HA切换策略，在面对突发故障表现更加灵活，保障业务高可用。采用RDS管控系统，极大减轻DBA运维压力美图数据库整体共包含20+个业务系统，数据库实例套数高达600+，数据量达到400TB，是当前公有云实例数最大的客户之一。每个DBA平均需要维护100+套数据库，需要投入大量的运维成本，极大影响了DBA的业务工作。为解决这个痛点，华为云采用RDS管控系统，日常运维工作均通过自动化平台完成，极大解放了DBA繁重运维工作，让DBA时间更聚焦在业务层面。在线迁移、多活容灾两不误，满足客户多样化业务场景美图业务种类繁多，业务场景复杂，需要数据库满足多样化业务需求，同时最大化减少业务停机时间。针对美图客户的要求，华为云采用数据复制服务DRS，对迁移、同步、订阅、灾备四种场景做了适配，满足客户多方面的业务需求。迁移方面，采用灰度分批切流迁移方案，针对不同业务场景定制灵活的迁移策略，逐步灰度切流完整验证云上各个服务组件。华为云使用DRS创建IDC- RDS双向同步链路，数据实时同步，出现问题能随时切回到IDC，最小化减少对客户业务影响。DRS迁移服务提供基于行数和内容多种数据对比能力，保证迁移数据的一致性。割接网关迁移方案 业务应用零改造，不受域名缓存和程序缓存影响，在线迁移稳定可靠。灾备方面，由于业务类型多样，美图对成本、可靠性、切换时间要求不一样，因此华为云提供了同城多活灾备、异地数据冷备容灾、异地数据热备容灾、异地多活容灾等多种容灾方式，满足了客户不同业务场景的灾备需求。美图解决方案架构图 “爱美之心人皆有之，而服务好用户的‘变美’需求，是美图最大的机会。未来，我们会更多借用华为云原生的能力，助力业务敏捷创新，提供更多与‘变美’相关的产品和服务，帮助用户在虚拟世界和现实世界都能全方位变美。”美图运维技术总监王关胜最后表示。值得一提的是，为了更好助力企业数字化、智能化转型，华为云数据库在品牌和业务层面进行了全新优化升级，推出了数据库自主品牌GaussDB，基于统一的计算存储分离架构，支持关系型和非关系型的数据库引擎，以及数据库工具类服务，以全场景云服务的方式持续服务好客户。未来，华为云GaussDB将为美图提供更高可靠、高可用、高安全、高扩展的服务能力，满足客户不同场景下的新需求，赋能美图智能化升级。数字化浪潮下，各行各业都在寻求转型，美图希望通过数字赋能，整合变美生态链，帮助用户全⽅位变美。华为云也将持续提供硬核技术和优秀服务，助力美图智能升级，进一步提升用户体验，让每个人都能简单变美。   【优惠活动】    【11·11云数据库专场】深度剖析行业痛点，提供全套解决方案，爆款产品低至2折，ECS+MySQL组合购还可享折上9折！更多惊喜猛戳→https://activity.huaweicloud.com/dbs_Promotion/index.html

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。子查询中常用的运算符1) IN子查询结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为：<表达式> [NOT] IN <子查询>语法说明如下。<表达式>：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。<子查询>：用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用 SELECT 语句实现子查询的多层嵌套。2) 比较运算符子查询比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为：<表达式> {= | < | > | >= | <= | <=> | < > | != }{ ALL | SOME | ANY} <子查询>语法说明如下。<子查询>：用于指定子查询。<表达式>：用于指定要进行比较的表达式。ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。其中，关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回 TRUE，否则返回 FALSE；关键字 SOME 和 ANY 是同义词，表示表达式只要与子查询结果集中的某个值满足比较关系，就返回 TRUE，否则返回 FALSE。3) EXIST子查询关键字 EXIST 所使用的子查询主要用于判断子查询的结果集是否为空。其语法格式为：EXIST <子查询>若子查询的结果集不为空，则返回 TRUE；否则返回 FALSE。子查询的应用【实例 1】在 tb_departments 表中查询 dept_type 为 A 的学院 ID，并根据学院 ID 查询该学院学生的名字，输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id IN     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type= 'A' ); +-------+ | name  | +-------+ | Dany  | | Henry | | Jane  | | Jim   | | John  | +-------+ 5 rows in set (0.01 sec)上述查询过程可以分步执行，首先内层子查询查出 tb_departments 表中符合条件的学院 ID，单独执行内查询，查询结果如下所示。mysql> SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type='A'; +---------+ | dept_id | +---------+ |       1 | |       2 | +---------+ 2 rows in set (0.00 sec)可以看到，符合条件的 dept_id 列的值有两个：1 和 2。然后执行外层查询，在 tb_students_info 表中查询 dept_id 等于 1 或 2 的学生的名字。嵌套子查询语句还可以写为如下形式，可以实现相同的效果。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id IN(1,2); +-------+ | name  | +-------+ | Dany  | | Henry | | Jane  | | Jim   | | John  | +-------+ 5 rows in set (0.03 sec)上例说明在处理 SELECT 语句时，MySQL 实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层子查询的结果作为外部查询的比较条件。【实例 2】与前一个例子类似，但是在 SELECT 语句中使用 NOT IN 关键字，输入的 SQL 语句和执行结果如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id NOT IN     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_type='A'); +--------+ | name   | +--------+ | Green  | | Lily   | | Susan  | | Thomas | | Tom    | +--------+ 5 rows in set (0.04 sec)提示：子查询的功能也可以通过连接查询完成，但是子查询使得 MySQL 代码更容易阅读和编写。【实例 3】在 tb_departments 表中查询 dept_name 等于“Computer”的学院 id，然后在 tb_students_info 表中查询所有该学院的学生的姓名，输入的 SQL 语句和执行过程如下所示。 mysql> SELECT name FROM tb_students_info     -> WHERE dept_id =     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_name='Computer'); +------+ | name | +------+ | Dany | | Jane | | Jim  | +------+ 3 rows in set (0.00 sec)【实例 4】在 tb_departments 表中查询 dept_name 不等于“Computer”的学院 id，然后在 tb_students_info 表中查询所有该学院的学生的姓名，输入的 SQL 语句和执行过程如下所示。mysql> SELECT name FROM tb_students_info     -> WHERE dept_id <>     -> (SELECT dept_id     -> FROM tb_departments     -> WHERE dept_name='Computer'); +--------+ | name   | +--------+ | Green  | | Henry  | | John   | | Lily   | | Susan  | | Thomas | | Tom    | +--------+ 7 rows in set (0.00 sec)【实例 5】查询 tb_departments 表中是否存在 dept_id=1 的供应商，如果存在，就查询 tb_students_info 表中的记录，输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS     -> (SELECT dept_name     -> FROM tb_departments     -> WHERE dept_id=1); +----+--------+---------+------+------+--------+------------+ | id | name   | dept_id | age  | sex  | height | login_date | +----+--------+---------+------+------+--------+------------+ |  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 | |  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 | |  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 | |  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 | |  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 | |  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 | |  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 | |  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 | |  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 | | 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 | +----+--------+---------+------+------+--------+------------+ 10 rows in set (0.00 sec)由结果可以看到，内层查询结果表明 tb_departments 表中存在 dept_id=1 的记录，因此 EXSTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录。EXISTS 关键字可以和条件表达式一起使用。【实例 6】查询 tb_departments 表中是否存在 dept_id=7 的供应商，如果存在，就查询 tb_students_info 表中的记录，输入的 SQL 语句和执行结果如下所示。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS     -> (SELECT dept_name     -> FROM tb_departments     -> WHERE dept_id=7); Empty set (0.00 sec)

MySQL 数据库中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。引发触发器执行的事件一般如下：增加一条学生记录时，会自动检查年龄是否符合范围要求。每当删除一条学生信息时，自动删除其成绩表上的对应记录。每当删除一条数据时，在数据库存档表中保留一个备份副本。触发程序的优点如下：触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。触发程序可以通过数据库中相关的表层叠修改另外的表。触发程序可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。触发器与表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。1) INSERT 触发器在 INSERT 语句执行之前或之后响应的触发器。使用 INSERT 触发器需要注意以下几点：在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问**入的行。在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改**入的值（只要具有对应的操作权限）。对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。2) UPDATE 触发器在 UPDATE 语句执行之前或之后响应的触发器。使用 UPDATE 触发器需要注意以下几点：在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。OLD 中的值全部是只读的，不能被更新。注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。3) DELETE 触发器在 DELETE 语句执行之前或之后响应的触发器。使用 DELETE 触发器需要注意以下几点：在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。OLD 中的值全部是只读的，不能被更新。总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。

在使用 MySQL 的过程中，MySQL 自带的函数可能完成不了我们的业务需求，这时候就需要自定义函数。自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。自定义函数与存储过程之间存在几点区别：自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。自定义函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。创建并使用自定义函数可以使用 CREATE FUNCTION 语句创建自定义函数。语法格式如下：CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )  RETURNS <类型>  <函数主体>语法说明如下：<函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。<参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。<函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。在 RETURN VALUE 语句中包含 SELECT 语句时，SELECT 语句的返回结果只能是一行且只能有一列值。若要查看数据库中存在哪些自定义函数，可以使用 SHOW FUNCTION STATUS 语句；若要查看数据库中某个具体的自定义函数，可以使用 SHOW CREATE FUNCTION<函数名> 语句，其中<函数名>用于指定该自定义函数的名称。【实例 1】创建存储函数，名称为 StuNameById，该函数返回 SELECT 语句的查询结果，数值类型为字符串类型，输入的 SQL 语句和执行结果如下所示。mysql> CREATE FUNCTION StuNameById()     -> RETURNS VARCHAR(45)     -> RETURN     -> (SELECT name FROM tb_students_info     -> WHERE id=1); Query OK， 0 rows affected (0.09 sec)注意：当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为反斜杠是 MySQL 的转义字符。成功创建自定义函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 调用用户自定义的函数，语法格式为：SELECT <自定义函数名> ([<参数> [,...]])【实例 2】调用自定义函数 StuNameById，查看函数的运行结果，如下所示。mysql> SELECT StuNameById(); +---------------+ | StuNameById() | +---------------+ | Dany          | +---------------+ 1 row in set (0.24 sec)修改自定义函数可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。删除自定义函数自定义函数被创建后，一直保存在数据库服务器上以供使用，直至被删除。删除自定义函数的方法与删除存储过程的方法基本一样，可以使用 DROP FUNCTION 语句来实现。语法格式如下：DROP FUNCTION [ IF EXISTS ] <自定义函数名>语法说明如下。<自定义函数名>：指定要删除的自定义函数的名称。IF EXISTS：指定关键字，用于防止因误删除不存在的自定义函数而引发错误。【实例 3】删除自定义函数 StuNameById，查看函数的运行结果，如下所示。mysql> DROP FUNCTION StuNameById; Query OK, 0 rows affected (0.09 sec) mysql> SELECT StuNameById(); ERROR 1305 (42000): FUNCTION test_db.StuNameById does not exist

2020-10-11：一条sql语句执行时间过长，应该如何优化？从哪些方面进行优化？#福大大架构师每日一题#

数据库系统由硬件和软件共同构成，硬件主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分则主要包括 DBMS、支持 DBMS 运行的操作系统，以及支持多种语言进行应用开发的访问技术等。本节主要介绍数据库涉及到的技术，包括数据库系统、SQL 语言和数据库访问接口。数据库系统数据库系统主要有以下 3 个组成部分：数据库：用于存储数据的地方。数据库管理系统：用于管理数据库的软件。数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库库的软件补充。数据库管理系统（Database Management System，DBMS）是位于操作系统与用户之间的一种操纵和管理数据库的软件，按照一定的数据模型科学地组织和存储数据，同时可以提供数据高效地获取和维护。数据库管理系统的主要功能包括以下几个方面。1) 数据定义功能DBMS 提供数据定义语言（Data Definition Language，DDL），用户通过它可以方便地对数据库中的数据对象进行定义。2) 数据操纵功能DBMS 还提供数据操纵语言（Data Manipulation Language，DML），用户可以使用 DML 操作数据，实现对数据库的基本操作，如查询、插入、删除和修改等。3) 数据库的运行管理数据库在建立、运用和维护时由数据库管理系统统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。例如：数据的完整性检查功能保证用户输入的数据应满足相应的约束条件；数据库的安全保护功能保证只有赋予权限的用户才能访问数据库中的数据；数据库的并发控制功能使多个用户可以在同一时刻并发地访问数据库的数据；数据库系统的故障恢复功能使数据库运行出现故障时可以进行数据库恢复，以保证数据库可靠地运行。4) 提供方便、有效地存取数据库信息的接口和工具编程人员可通过编程语言与数据库之间的接口进行数据库应用程序的开发。数据库管理员（Database Administrator，DBA）可通过提供的工具对数据库进行管理。数据库管理员是维护和管理数据库的专门人员。5) 数据库的建立和维护功能数据库功能包括数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库的重组织功能和性能监控、分析功能等。这些功能通常由一些使用程序来完成。数据库系统是指在计算机系统中引入数据库后的系统。一个完整的数据库系统（Database System，DBS）一般由数据库、数据库管理系统、应用开发工具、应用系统、数据库管理员和用户组成。完整的数据库系统结构关系如图所示：                                                     了解SQL语言MySQL 服务器正确安装以后，就已经完成了一个完整的 DBMS 的搭建，可以通过命令行管理工具或者图形化的管理工具对 MySQL 数据库进行操作。这种对数据库进行查询和修改操作的语言叫做 SQL（Structured Query Language，结构化查询语言）。SQL 语言是目前广泛使用的关系数据库标准语言，是各种数据库交互方式的基础。SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。与其他程序设计语言（如 C语言、Java 等）不同的是，SQL 由很少的关键字组成，每个 SQL 语句通过一个或多个关键字构成。SQL 具有如下优点。一体化：SQL 集数据定义、数据操作和数据控制于一体，可以完成数据库中的全部工作。使用方式灵活：SQL 具有两种使用方式，可以直接以命令方式交互使用；也可以嵌入使用，嵌入C、C++、Fortran、COBOL、Java 等语言中使用。非过程化：只提操作要求，不必描述操作步骤，也不需要导航。使用时只需要告诉计算机“做什么”，而不需要告诉它“怎么做”，存储路径的选择和操作的执行由数据库管理系统自动完成。语言简洁、语法简单：该语言的语句都是由描述性很强的英语单词组成，而且这些单词的数目不多。SQL 包含以下 4 部分：数据定义语言（DDL）：DROP、CREATE、ALTER 等语句。数据操作语言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。数据查询语言（DQL）：SELECT 语句。数据控制语言（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK 等语句。下面是一条 SQL 语句的例子，该语句声明创建一个名叫 students 的表：CREATE TABLE students (     student_id INT UNSIGNED,     name VARCHAR(30) ,     sex CHAR(1),     birth DATE,     PRIMARY KEY(student_id) );该表包含 4 个字段，分别为 student_id、name、sex、birth，其中 student_id 定义为表的主键。现在只是定义了一张表格，但并没有任何数据，接下来这条 SQL 声明语句，将在 students 表中插入一条数据记录：INSERT INTO students (student_id, name, sex, birth) VALUES (888, '华为云MySQL教程', '1', '2020-11-11');执行完该 SQL 语句之后，students 表中就会增加一行新记录，该记录中字段 student_id 的值为“888”，name 字段的值为“华为云MySQL教程”。sex 字段值为“1”，birth 字段值为“2020-011-11”。再使用 SELECT 查询语句获取刚才插入的数据，如下：SELECT name FROM students WHERE student_id=41048101; +--------------+ | name         | +--------------+ |华为云MySQL教程| +--------------+上面简单列举了常用的数据库操作语句，在这里留下一个印象即可，后面我们会详细介绍这些知识。注意：SQL 语句不区分大小写，许多 SQL 开发人员习惯对 SQL 本身的关键字进行大写，而对表或者列的名称使用小写，这样可以提高代码的可阅读性和可维护性。本教程也按照这种方式组织 SQL 语句。大多数数据库都支持通用的 SQL 语句，同时不同的数据库具有各自特有的 SQL 语言特性。数据库访问接口不同的程序设计语言会有各自不同的数据库访问接口，程序语言通过这些接口，执行 SQL 语句，进行数据库管理。主要的数据库访问接口主要有  ODBC、JDBC、ADO.NET 和 PDO。ODBCODBC（Open Database Connectivity，开放数据库互连）为访问不同的 SQL 数据库提供了一个共同的接口。ODBC 使用 SQL 作为访问数据的标准。这一接口提供了最大限度的互操作性。一个应用程序可以通过共同的一组代码访问不同的 SQL 数据库管理系统。一个基于 ODBC 的应用程序对数据库的操作不依赖任何 DBMS，不直接与 DBMS 打交道，所有的数据库操作由对应的 DBMS 的 ODBC 驱动程序完成。也就是说，不论是 MySQL 还是 Oracle 数据库，均可用 ODBC API 进行访问。由此可见，ODBC 的最大优点是能以统一的方式处理所有的数据库。JDBCJava Data Base（JDBC，Java 数据库连接）用于 Java 应用程序连接数据库的标准方法，是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。ADO.NETADO.NET 是微软在 .NET 框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET 提供了对关系数据、XML 和应用程序的访问，允许和不同类型的数据源以及数据库进行交互。PDOPDO（PHP Data Object）为 PHP 访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。PDO 是 PHP 5 新加入的一个重大功能。

MySQL处理数据的基本原则是“垃圾进来，垃圾出去”，通俗一点说就是你传给 MySQL 什么样的数据，它就会存储什么样的数据。如果在存储数据时没有对它们进行验证，那么在把它们检索出来时得到的就不一定是你所期望的内容。 有几种 SQL 模式可以在遇到“非正常”值时抛出错误，如果你对其他数据库管理系统比较熟悉，会发现这种行为和其他的数据库管理系统很像。 下面介绍 MySQL 默认情况下如何处理非正常数据和启用各种 SQL 模式时会对数据处理产生哪些影响。 默认情况下，MySQL 会按照以下规则来处理越界（即超出取值范围）的值和其他非正常值：对于数值列或 TIME 列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点，并把结果值存储起来。对于除 TIME 列以外的其他类型列，非法值会被转换成与该类型一致的“零”值。对于字符串列（不包括 ENUM 或 SET），过长的字符串将被截断到该列的最大长度。给 ENUM 或 SET 类型列进行赋值时，需要根据列定义里给出的合法取值列表进行。如果把不是枚举成员的值赋给 ENUM 列，那么列的值就会变成空字符串。如果把包含非集合成员的子字符串的值赋给 SET 列，那么这些字符串会被清理，剩余的成员才会被赋值给列。 如果在执行增删改查等语句时发生了上述转换，那么 MySQL 会给出警告消息。在执行完其中的某一条语句之后，可以使用 SHOW WARNINGS 语句来查看警告消息的内容。 如果需要在插入或更新数据时执行更严格的检查，那么可以启用以下两种 SQL 模式中的一种：SET sql_mode = 'STRICT_ALL_TABLES' ;SET sql_mode = 'STRICT_TRANS_TABLES';对于支持事务的表，这两种模式都是一样的。如果发现某个值无效或缺失，那么会产生一个错误，并且语句会中止执行，并进行回滚，就像什么事都没发生过一样。 对于不支持事务的表，这两种模式有以下效果。 1) 对于这两种模式，如果在插入或修改第一个行时，发现某个值无效或缺失，那么结果会产生一个错误，语句会中止执行，就像什么事都未发生过一样。 这跟事务表的行为很相似。 2) 在用于插入或修改多个行的语句里，如果在第一行之后的某个行出现了错误，那么会出现某些行被修改的情况。这两种模式决定着，这条语句此时此刻是要停止执行，还是要继续执行。在 STRICT_ALL_TABLES 模式下，会抛出一个错误，并且语句会停止执行。因为受该语句影响的许多行都已被修改，所以这将会导致“部分更新”问题。在 STRICT_TRANS_TABLES 模式下，对于非事务表，MySQL 会中止语句的执行。只有这样做，才能达到事务表那样的效果。只有当第一行发生错误时，才能达到这样的效果。如果错误在后面的某个行上，那么就会出现某些行被修改的情况。由于对于非事务表，那些修改是无法撤销的，因此 MySQL 会继续执行该语句，以避免出现“部分更新”的问题。它会把所有的无效值转换为与其最接近的合法值。对于缺失的值，MySQL 会把该列设置成其数据类型的隐式默认值， 通过以下模式可以对输入的数据进行更加严格的检查：ERROR_ FOR_ DIVISION_ BY_ ZERO：在严格模式下，如果遇到以零为除数的情况，它会阻止数值进入数据库。如果不在严格模式下，则会产生一条警告消息，并插入 NULL。NO_ ZERO_ DATE：在严格模式下，它会阻止“零”日期值进入数据库。NO_ ZERO_ IN_ DATE：在严格模式下，它会阻止月或日部分为零的不完整日期值进入数据库。 简单来说，MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度、类型等。比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下 MySQL 不会报错。如果定义了 char 或 varchar 类型的字段，当写入或更新的数据超过了定义的长度也不会报错。 虽然我们会在代码中做数据校验，但一般认为非严格模式对于编程来说没有任何好处。MySQL开启严格模式从一定程序上来讲也是对我们代码的一种测试，如果我们没有开启严格模式并且在开发过程中也没有遇到错误，那么在上线或代码移植的时候将有可能出现不兼容的情况，因此在开发过程做最好开启 MySQL 的严格模式。 可通过select @@sql_mode;命令查看当前是严格模式还是非严格模式。 例如，如果想让所有的存储引擎启用严格模式，并对“被零除”错误进行检查，那么可以像下面这样设置 SQL 模式：SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ; 如果想启用严格模式，以及所有的附加限制，那么最为简单的办法是启用 TRADITIONAL 模式：SET sql_ mode ‘TRADITIONAL' ;TRADITIONAL 模式的含义是“启用严格模式，当向 MySQL 数据库插入数据时，进行数据的严格校验，保证错误数据不能插入。用于事务表时，会进行事务的回滚”。 可以选择性地在某些方面弱化严格模式。如果启用了 SQL 的 ALLOW_ INVALID_ DATES 模式，那么MySQL将不会对日期部分做全面检查。相反，它只会要求月份值在 1~12 之间，而天数处于 1~31 之间，即允许像‘2000-02-30’或‘2000-06-31’这样的无效值。 另一个制止错误的办法是在 INSERT 或 UPDATE 语句里使用 IGNORE 关键字。这样那些会因无效值而导致错误的语句，将只会导致警告的出现。这些选项能让你灵活地为你的应用选择正确的有效性检查级别。

银行业务中，有一条记账原则，即有借有贷，借贷相等。为了保证这种原则，每发生一笔银行业务，就必须确保会计账目上借方科目和贷方科目至少各记一笔，并且这两笔账要么同时成功，要么同时失败。如果出现只记录了借方科目，或者只记录了贷方科目的情况，就违反了记账原则。会出现记错账的情况。在银行的日常业务中，只要是同一银行（如都是中国农业银行，简称农行），一般都支持账户间的直接转账。因此，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户的存款就要增加相应的金额。下面，在 MySQL 数据库中模拟一下上述提及的转账问题。假如要从张三的账户直接转账 500 元到李四的账户。首先需要创建账户表，存放用户张三和李四的账户信息。创建账户表和插入数据的 SQL 语句和运行结果如下所示：mysql> CREATE DATABASE mybank; Query OK, 1 row affected (0.02 sec) mysql> USE mybank; Database changed mysql> CREATE TABLE bank(     -> customerName VARCHAR(20),   #用户名     -> currentMoney DECIMAL(10,2)    #当前余额     -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.26 sec) mysql> INSERT INTO bank (customerName,currentMoney) VALUES('张三',1000);; Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO bank (customerName,currentMoney) VALUES('李四',1); Query OK, 1 row affected (0.08 sec)查询 bank 数据表的 SQL 语句和运行结果如下：mysql> SELECT * FROM bank; +--------------+--------------+ | customerName | currentMoney | +--------------+--------------+ | 张三         |      1000.00 | | 李四         |         1.00 | +--------------+--------------+ 2 rows in set (0.02 sec)结果显示，张三和李四两个账户的余额总和为 1000+1=1001 元。下面开始模拟实现转账功能。从张三的账户直接转账 500 元到李四的账户，可以使用 UPDATE 语句分别修改张三的账户和李四的账户。张三的账户减少 500 元，李四的账户增加 500 元， SQL 语句如下所示：/*转账测试：张三转账给李四 500 元*/ #张三的账户少 500 元，李四的账户多 500 元 UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三'; UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';正常情况下，执行以上的转账操作后，余额总和应保持不变，仍为 1001 元。但是，如果在这个过程的其中一个环节出现差错，如在张三的账户减少 500 元之后，这时发生了服务器故障，李四的账户没有立即增加 500 元，此时，第三方读取到两个账户的余额总和变为 500+1=501 元，即账户总额间少了 500 元。MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作**成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。MySQL 通过事务保证了数据的一致性。上述提到的转账过程就是一个事务，它需要两条 UPDATE 语句来完成。这两条语句是一个整体，如果其中任何一个环节出现问题，则整个转账业务也应取消，两个账户中的余额应恢复为原来的数据，从而确保转账前和转账后的余额总和不变，即都是 1001 元。

 MySQL 支持 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 几种存储引擎，不同存储引擎的数据表在文件系统中的表示也各不相同。MySQL 中的每一个数据表在磁盘上至少被表示为一个文件，即存放着该数据表结构定义的 .frm 文件。不同的存储引擎还有其它用来存放数据和索引信息的文件。从 MySQL 8.0 版本开始，frm 表结构定义文件被取消，MySQL 把表结构信息都写到了系统表空间。下面介绍不同存储引擎的数据表在文件系统中是如何表示的。MyISAMMyISAM 存储引擎的数据表在数据库目录里使用 3 个文件来代表，这些文件的基本名与数据表的名字相同，扩展名则表明了文件的具体用途。这三个文件的扩展名分别是：.frm：表结构定义文件，存放着该数据表的结构定义。.MYD：MY Data 的缩写，数据文件，存放着该数据表中各个行的数据。.MYI： MY Index 的缩写，索引文件，存放着该数据表的全部索引信息。下面创建存储引擎为 MyISAM 的 tb_myisam 表，SQL 语句如下。mysql> SET default_storage_engine=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE tb_myisam(     -> id INT     -> ); Query OK, 0 rows affected (0.03 sec)本节中所有创建的不同存储引擎的表在文件系统（数据目录）中的表示在文章末尾展示。MERGEMERGE 存储引擎的数据表其实是一个逻辑结构。它代表着由一组结构完全相同的 MyISAM 数据表所构成的集合。有关的查询命令会把它当作一个大数据表来对待。MERGE 存储引擎的数据表除了拥有存储表结构定义的 .frm 文件以外，还有一个扩展名为 .mgr 的文件，这个文件里不保存数据，而是数据的来源地。通俗的说，就是一份由多个 MyISAM 数据表的名单构成的 MERGE 数据表。下面创建存储引擎为 MERGE 的 tb_merge 表，SQL 语句如下。mysql> SET default_storage_engine=Merge; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tb_merge(     -> id INT     -> ); Query OK, 0 rows affected (0.02 sec)InnoDB对于 InnoDB 存储引擎的数据表，一个表对应两个文件，一个是 *.frm，存储表结构信息；一个是 *.ibd，存储表中数据。下面创建存储引擎为 InnoDB 的 tb_innodb 表，SQL 语句如下。mysql> SET default_storage_engine=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tb_innodb(     -> id INT     -> ); Query OK, 0 rows affected (0.10 sec)MemoryMemory 存储引擎的数据表是创建在内存中的数据表。因为 MySQL 服务器把 Memory 数据表的数据和索引都存放在了内存中而不是硬盘上，所以除了相应的 .frm 文件外，Memory 引擎表在文件系统里没有其它相应的代表文件。下面创建存储引擎为 Memory 的 tb_memory 表，SQL 语句如下。mysql> SET default_storage_engine=Memory; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tb_memory(     -> id INT     -> ); Query OK, 0 rows affected (0.03 sec)ArchiveArchive 存储引擎的数据表除了拥有 .frm 表结构定义文件外，还有一个扩展名为 .arz 的数据文件，用来存储历史归档数据。执行优化操作时可能还会出现一个扩展名为 .arn 的文件。下面创建存储引擎为 Archive 的 tb_archive 表，SQL 语句如下。mysql> SET default_storage_engine=Archive; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tb_archive(     -> id INT     -> ); Query OK, 0 rows affected (0.04 sec)CSV与其它类型的存储引擎相同，CSV 引擎表也会包含一个 .frm 表结构定义文件，此外还会创建一个扩展名为 .CSV 的数据文件。这个文件是 CSV 格式的文本文件，用来保存表中的实际数据。.CSV 文件可以直接在 Excel 中打开，或者是使用其它文件编辑工具查看。另外，还有一个同名的元信息文件，文件扩展名为 .CSM，用来保存表的状态及表中保存的数据量。由于 CSV 文件可被直接编辑，如果操作得当，可以不通过 SQL 语句直接修改 CSV 文件中的内容。CSV 存储引擎基于 CSV 格式文件存储数据，由于自身文件格式的原因，所有列必须强制指定 NOT NULL。下面创建存储引擎为 CSV 的 tb_csv 表，SQL 语句如下。mysql> SET default_storage_engine=csv; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE tb_csv(     -> id INT NOT NULL,     -> name CHAR(10) NOT NULL     -> ); Query OK, 0 rows affected (0.04 sec)BLACKHOLE由于在 BLACKHOLE 存储引擎的数据表中写入任何数据都会消失，所以除了 .frm 文件，BLACKHOLE 引擎表没有其他相应的代表文件。下面创建存储引擎为 BLACKHOLE 的 tb_blackhole 表，SQL 语句如下。mysql> SET default_storage_engine=BLACKHOLE; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tb_blackhole(     -> id INT     -> ); Query OK, 0 rows affected (0.03 sec)下图是不同存储引擎的数据表在文件系统中的表示。

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。1. 查询语句中使用LIKE关键字在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。例 1为了便于理解，我们先查询 tb_student 表中的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_student; +----+------+------+------+ | id | name | age  | sex  | +----+------+------+------+ |  1 | 张三 |   12 | 男   | |  2 | 李四 |   12 | 男   | |  3 | 王五 |   13 | 女   | |  4 | 张四 |   13 | 女   | |  5 | 王四 |   15 | 男   | |  6 | 赵六 |   12 | 女   | +----+------+------+------+ 6 rows in set (0.03 sec)下面在查询语句中使用 LIKE 关键字，且匹配的字符串中含有“%”符号，使用 EXPLAIN 分析查询情况，SQL 语句和运行结果如下：mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '%四'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 16.67         Extra: Using where 1 row in set, 1 warning (0.01 sec) mysql> CREATE INDEX index_name ON tb_student(name); Query OK, 6 rows affected (0.13 sec) mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: range possible_keys: index_name           key: index_name       key_len: 77           ref: NULL          rows: 1      filtered: 100.00         Extra: Using index condition 1 row in set, 1 warning (0.00 sec)第一个查询语句执行后，rows 参数的值为 6，表示这次查询过程中查询了 6 条记录；第二个查询语句执行后，rows 参数的值为 1，表示这次查询过程只查询 1 条记录。同样是使用 name 字段进行查询，因为第一个查询语句的 LIKE 关键字后的字符串是以“%”开头的，所以第一个查询语句没有使用索引，而第二个查询语句使用了索引 index_name。2. 查询语句中使用多列索引多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。例 2在 name 和 age 两个字段上创建多列索引，并验证多列索引的使用情况，SQL 语句和运行结果如下：mysql> CREATE INDEX index_name_age ON tb_student(name,age); Query OK, 6 rows affected (0.11 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: range possible_keys: index_name_age           key: index_name_age       key_len: 77           ref: NULL          rows: 1      filtered: 100.00         Extra: Using index condition 1 row in set, 1 warning (0.05 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE age LIKE '12'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 16.67         Extra: Using where 1 row in set, 1 warning (0.00 sec)第一条查询语句的查询条件使用了 name 字段，分析结果显示 rows 参数的值为 1，且查询过程中使用了 index_name_age 索引。第二条查询语句的查询条件使用了 age 字段，结果显示 rows 参数的值为 6，且 key 参数的值为 NULL，这说明第二个查询语句没有使用索引。因为 name 字段是多列索引的第一个字段，所以只有查询条件中使用了 name 字段才会使 index_name_age 索引起作用。3. 查询语句中使用OR关键字查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。例 3下面演示 OR 关键字的使用。mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or sex='男'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: index_name,index_name_age           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 30.56         Extra: Using where 1 row in set, 1 warning (0.06 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or id='12'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: index_merge possible_keys: PRIMARY,index_name,index_name_age           key: index_name,PRIMARY       key_len: 77,4           ref: NULL          rows: 2      filtered: 100.00         Extra: Using union(index_name,PRIMARY); Using where 1 row in set, 1 warning (0.01 sec)由于 sex 字段没有索引，所以第一条查询语句没有使用索引；name 字段和 id 字段都有索引，所以第二条查询语句使用了 index_name 和 PRIMARY 索引 。总结使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE 关键字配置的字符串不能以“%”开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用 OR 关键字时，OR 关键字连接的所有条件都必须使用索引。