标识符可以用反引号引住，也可以不引住，如果标识符包含特殊字符或者本身是保留字，则必须用反引号引住。MySQL内部使用Unicode(UTF-8)保存标识符，在BMP(BasicMultilingualPlane)中的Unicode字符可出现在标识符中，但增补字符不允许。
1、允许出现在无反引号标识符中的字符：
2、允许出现在反引号标识符中的字符，包括除了U+0000外的全部BMP字符：
3、ASCIINUL(U+0000)字符和高于U+10000的增补字符不允许出现在标识符中，无论是否带反引号。
4、标识符可以以数字开头。除非反引号引住否则不允许全数字的标识符。
5、库名、表名、列表不能以空字符结尾。
表名、列名可以是全数字，但必须用反引号引起来，否则会报错
数据库、表、列和索引的名称最长可达64个字符。别名最长可达256个字符。
下面的sql会报错，因为添加的列名current_time是MySQL的一个函数名
altertablet1addcolumncurrent_timevarchar(255);报错：[42000][1064]YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear‘current_timevarchar(255)’atline1
解决：使用反引号将current_time引起来：
sql1:selectcount(*)fromuserwherescore>90;优化为按主键id分段count后相加sql2:
selectsum(cnt)from(selectcount(*)cntfromtable_awhereid<1000000andageisnotnullunionallselectcount(*)cntfromtable_awhereid>=1000000andid<2000000andageisnotnull)t;2亿8千万数据，符合条件的约3000万sql1执行了6个小时还没结束
从information_schema.tables查询TABLE_ROWS对于InnoDB存储引擎来说是个近似值。selectTABLE_ROWSfrominformation_schema.tableswheretable_schema=’db_name’andtable_name=’table_a’;
本质和从information_schema.tables查询TABLE_ROWS一样，对于InnoDB存储引擎来说是个近似值。
2亿8千万数据，符合条件的有大约1千万数据sql1执行了6个小时还没结束sql2半小时执行完了。
当MySQL中字段为int类型时，搜索条件wherenum=’123’与wherenum=123都可以使用该字段的索引。当MySQL中字段为varchar类型时，搜索条件wherenum=’123’可以使用索引，wherenum=123不可以使用索引。
所以：字段类型不同会有隐式类型转换，可能导致索引失效。
这一点也是MySQLint和varchar类型选择的一个依据：1、除了int效率较高之外。2、int类型字段建索引后可以匹配int和varchar条件，但varchar类型字段建索引后只能匹配varchar条件如果某个字段存储的是int，那就在MySQL中定义为int，而不要使用varchar。
假如user表使用自增id作为主键，且创建后create_time不会变，则id排序和create_time排序的结果是一样的，可使用id排序代替create_time排序。因为create_time上即使有索引也是二级索引，需要回表找到主键id才行。
如果像下面这样循环分页查，随着offset的增加，查询会变的越来越慢，因为mysql是查出匹配条件的全部数据后再切分的。
select*fromuserwherexx=xxorderbyxxlimit$offset,100;正确的循环分页查询方法应该是把offset转换为whereid条件，每次不断改变条件，使用whereid条件和limit来分页，不要使用偏移量offset。典型的比如使用id，每次记录上一页最后一个id当做条件
SELECT*FROMtabletWHEREcreate_time>=$t1ANDcreate_time<=$t2ORDERBYidASCLIMIT$offset,$count;可以内部对其进行优化，将offset分页改写为whereid条件
selectdistinctu.*fromuserujoinuser_tagutonu.id=ut.user_idwhereut.tag_idin(1,2,3)orderbyu.birthdaydesclimit10;数据量：200万user，300万user_tag，这种索引该怎么建？最终建立的索引
user_tag表1、在user_id列上创建索引，为了加速”查某人的标签”2、在(tag_id,user_id)列上创建联合索引，为了查某标签下有多少人altertableuser_tagaddindexidx_user_id(user_id),addindexidx_tag_user(tag_id,user_id);
网上有人做了实验，结果是：关联表分别创建user_id与tag_id的单列索引idx_user,idx_tag最优。
有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USEINDEX对查询语句进行设置。SELECT*FROMTABLE1USEINDEX(FIELD1,FIELD2)…无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。
如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNOREINDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNOREINDEX是选择不被考虑的索引。SELECT*FROMTABLE1IGNOREINDEX(FIELD1,FIELD2)…在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。
有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCEINDEX来完成这个功能。SELECT*FROMTABLE1FORCEINDEX(FIELD1)…以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。
MySQL的like查询是不区分大小写的有时候，我们需要区分大小写的是，该怎么办呢？
一、一种方法是在查询时指定大小写敏感，在like的后面加个binary就可以了
like子句中%匹配多个字符，_匹配单个字符。如果要匹配的内容本身包含%和_怎么办呢？
可以使用escape指定转义字符，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
select*fromuserwherenamelike‘%li/_%’escape‘/‘;select*fromuserwherenamelike‘%li/%%’escape‘/‘;
例：
1、查找long型字符串create_time以000结尾的记录
orderbyfield可以按指定的顺序排序，最好搭配in一起使用
SELECT*FROMMyTablewhereidin(5,3,7,1)ORDERBYFIELD(`id`,5,3,7,1)好像如果是数字排序的话，不加in也可以。
按指定的姓名顺序排序
表结构定义：
要求对于user_id相同的记录，选出create_time最新的一条，单个查的话很简单，排下序就行，现在要求根据user_id批量查。sql如下，对于外层的每条记录，去内层查有没有create_time更大的，没有的话，选出这条记录：
表结构同上，根据user_id批量查询，对于每个user_id，如果存在多条数据，选择id最大的那条记录，即最后插入的记录，id为自增主键。
用一个SQL语句完成不同条件的分组例如：stu_cls表有字段nameclassscore，用一条sql查询每个class里及格和不及格的学生人数，结果形式classnum(>=60)，num(<60)
selectu.id,casewhenurt.idisnotnullthenurt.user_identityelse1endfromuseruleftjoinuser_role_transactionurtonurt.user_id=u.idWHEREurt.idisnullor(urt.user_id=u.idANDNOTexists(SELECT*FROMuser_role_transactionurt2WHEREurt2.user_id=urt.user_idANDurt2.id>urt.id))orderbyu.id;使用了casewhen，对于在user_role_transaction表中的user（即urt.idisnotnull）结果为urt.user_identity，否则（即不在user_role_transaction表中的user）结果为固定值1
需求：user表的identity字段是当前用户身份，新加的字段user_role_transaction保存的是用户身份变更历史，每变动一次插入一条新数据之前只有user_role_transaction历史表，user表中没有identity字段，现在刚加上，要用user_role_transaction的user_identity字段初始化user表的identity字段，规则是：对于user_id在user_role_transaction表中的user，把user表的identity字段设置为此user_id在user_role_transaction表中的最新一个值（即id最大的）对于user_id不在user_role_transaction表中的user，把user表的identity字段设置为固定值1
sql如下：
UPDATEuseruleftjoinuser_role_transactionurtonurt.user_id=u.idsetu.identity=casewhenurt.idisnotnullthenurt.user_identityelse1endWHEREurt.idisnullor(urt.user_id=u.idANDNOTexists(SELECT*FROMuser_role_transactionurt2WHEREurt2.user_id=urt.user_idANDurt2.id>urt.id));解释：使用了updatejoin根据查询条件更新，由于user表中的数据有可能不在user_role_transaction表中，使用了leftjoin左外连接来保留user中非关联数据筛选条件是urt.id为空（即左外关联后user_role_transaction表对应列是空的，即不在user_role_transaction表中的user），或者关联后user_role_transaction中id最大的（这里用了个notexists筛选没有比他id更大的）set赋值语句使用了casewhen，对于在user_role_transaction表中的user（即urt.idisnotnull）设置为选出的urt.user_identity，否则（即不在user_role_transaction表中的user）设置为1
MySQL中，where中不能使用别名，groupby/having和orderby中可以使用别名Oracle/Hive/标准SQL/达梦数据库中，where/groupby/having中不能使用列别名，orderby中可以使用别名解决方法是将别名放到子查询中，例如select….from(selectcol1cccfromtable)whereccc>1
这是由sql语句的执行顺序决定的：
例1、where中使用列别名，报错[42S22][1054]Unknowncolumn‘times’in‘whereclause’
比如想查询指定user_id的没有订单号的数据，如果写成下面这样就大错特错了：
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。在使用leftjion时，on和where条件的区别如下：1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有leftjoin的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
1、如下两个sql是相等的，都是带有on条件的innerjoin
select*fromAajoinBbona.id=b.a_id;select*fromAa,Bbwherea.id=b.a_id;2、如下两个sql也是相等的，都是不带on条件的crossjoin，结果是两表的笛卡尔积，行数等于A表行数乘以B表行数
select*fromAajoinBb;select*fromAa,Bb;但更推荐使用join语法
比如我们想查询用户地址表user_address中的user_id不在user表中的脏数据，这些脏数据可能是由于user_address表的user_id字段没加外键约束导致的。可以用下面的notin子句进行条件筛选：
SELECT*FROMuser_addresswhereuser_idnotin(selectidfromuser);或者使用leftjoin左外连接把user_address表中独有的数据保留下来，结果中右边user表中没数据的都是null，直接用user的某个字段是否null判断即可
select*fromuser_addressasualeftjoinuserasuONua.user_id=u.idwhereu.idisnull;可以用关联后的user表的任意字段是否null进行判断。用leftjoin显得更高端一点儿。
InMySQL,JOIN,CROSSJOIN,andINNERJOINaresyntacticequivalents(theycanreplaceeachother).InstandardSQL,theyarenotequivalent.INNERJOINisusedwithanONclause,CROSSJOINisusedotherwise.
当没有on连接条件时，crossjoin和join以及innerjoin都是笛卡尔积。例如
select*fromAcrossjoinB;可以替换为innerjoin或join，和标准sql一样，是做笛卡尔积，结果个数是表A行数乘以表B行数
由于mysql将这三者视为等同，所以crossjoin也可以加on条件，而标准sql中crossjoin是不能加条件的。例如
select*fromAasacrossjoinBasbona.id=b.a_id;和使用innerjoin或join完全相同。
mysql中没有fulljoin语法，下面的sql会报语法错误：
select*fromAasafulljoinBasbona.id=b.a_id;可使用union并集代替全连接。
MySQLUNION用于把来自多个SELECT语句的结果组合到一个结果集合中。语法为：
SELECTcolumn,...FROMtable1UNION[ALL]SELECTcolumn,...FROMtable2...在多个SELECT语句中，对应的列应该具有相同的字段属性，且第一个SELECT语句中被使用的字段名称也被用于结果的字段名称。
注意：1、union会去除结果中的重复记录，这里的重复指的是所有字段完全相同，有任意字段不同也算作不同记录。2、第一个SELECT语句中被使用的字段名称也被用于结果的字段名称3、各SELECT语句字段名称可以不同，但字段属性必须一致。
当使用UNION时，MySQL会把结果集中重复的记录删掉，而使用UNIONALL，MySQL会把所有的记录返回，且效率高于UNION。
使用UNIONALL的时候，只是单纯的把各个查询组合到一起而不会去判断数据是否重复。因此，当确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用UNIONALL以提高查询效率。
为什么用unionall效率更高？因为他不需要去重
+----------+|count(*)|+----------+|6||5||5|+----------+要求和的话在外层再加一次sum()或count()即可
注意此时是将a,b,c三列所有不同的组合全部列出来，而不仅仅只是distincta
如果想distinct只作用于a列，同时又能查出对应的b和c列，可以使用groupbya来代替，此时如果唯一的a列对应的b，c列值不同，查出的结果可能具有不确定性。
groupby将查询结果按某一列或多列的值分组，值相等的为一组
where子句中不能使用聚集函数作为条件表达式
user_label_mapping是userid和标签id映射表user_label是标签表一个user可以有多个标签，查出同一user的多个标签并串接为逗号分隔字符串
select中指定的字段要么就要包含在groupby语句的后面，作为分组的依据；要么就要被包含在聚合函数中。含有groupby子句的select中指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。
groupby后的orderby子句中的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。例如
select*fromusergroupbyname;报错ERROR1055(42000):Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn‘x.x’whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by
原因是SQL标准中不允许SELECT列表，HAVING条件语句，或ORDERBY语句中出现分组字段和聚集函数之外的字段。而MySQL中有一个状态ONLY_FULL_GROUP_BY来标识是否遵从这一标准，默认为开启状态。
2、通过ANY_VALUE()来改造查询语句以避免报错使用ANY_VALUE()包裹的值不会被检查，跳过该错误。SELECTgender,ANY_VALUE(last_name)FROMemployeesGROUPBYgender
groupby或having子句中不能使用列的别名例：
SELECTaasc,sum(b)FROMtestGROUPBYc会提示错误，groupbyc改成groupbya就行。
我在开发中遇到的错误：
\G的作用是将查到的结构旋转90度变成纵向
使用STARTTRANSACTION,BEGIN来显式地开启一个事务。在显式开启事务后，在默认设置下（即参数completion_type等于0），MySQL会自动地执行SETAUTOCOMMIT=0的命令，并在COMMIT或ROLLBACK结束一个事务后执行SETAUTOCOMMIT=1
starttransction或begin：显示的开启一个事务commit或commitwork：commitwork与completion_type的关系，commitwork是用来控制事务结束后的行为，是chain还是release的，可以通过参数completion_type来控制，默认为0（或者NO_CHAIN），表示没有任何操作与commit效果一样。
1、mysql使用InnoDB的引擎，那么是自动开启事务的，也就是每一条sql都是一个事务（除了select）。2、由于第一条的原因，所以我们需要autocommit为on，否则每个query都要写一个commit才能提交。3、在mysql的配置中，默认缺省autocommit就是为on，这里要注意，不用非要去mysql配置文件中显示地配置一下。4、最关键的来了，当我们显示地开启一个事务，也就是写了begin的时候，autocommit对此事务不构成影响。而不是网上大家说的，必须要写一个query临时设置autocommit为off，否则比如三个query只能回滚最后一个query，这是完全不对的。
1、setautocommit=0关闭当前会话中事务的自动提交，需要手动commit或者rollback，相当于开启一个全局的事务。在mysql的事务中，默认autocommit=1，每一次sql操作都被认为是一个单次的事务，被隐式提交
2、starttransaction挂起autocommit的状态，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。挂起autocommit的意思是保存autocommit的当前状态，然后starttransaction，直到commitorrollback结束本次事务，再恢复之前挂起的autocommit的状态。
如果starttransaction前autocommit=1，则完成本次事务后autocommit还是1如果starttransaction前autocommit=0，则完成本次事务后autocommit还是0，接下来的操作你仍需手动commit才可以提交。
SHOWPLUGINS查看支持的插件，或者从INFORMATION_SCHEMA.PLUGINS表中查看支持的插件
如果在INSERT语句末尾指定了ONDUPLICATEKEYUPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARYKEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。例如，如果列a为主键或拥有UNIQUE索引，并且包含值1，则以下两个语句具有相同的效果：
truncate是ddl语句，需要drop权限。
truncatetablewp_comments;deletefromwp_comments;这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：
关于加锁：
比如从user表中删除数据，如果还有其他表引用此行数据会导致删除失败：
[23000][1451]Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`uds`.`table_name`,CONSTRAINT`user_fk_1`FOREIGNKEY(`user_id`)REFERENCES`user`(`id`))如果忽略数据一致性，强行删除的话，可以暂时关闭外键检查：
mysqldeleletable语句使用别名：语法：
deletetfromtabletwheret.column=value;或者不使用别名：
deletefromtablewherecolumn=value;但如果使用别名，delete后一定要加别名t，否则在某些严格语法检查的情况下会报错。
用in
deletefromtable1whereuser_idin(SELECTuser_idFROMtable1GROUPBYuser_idHAVINGcount(*)>1);或者用exists
deletefromtable1whereexists(SELECTuser_idFROMtable1GROUPBYuser_idHAVINGcount(*)>1);mariadb中都会报错：[HY000][1093]Table‘table1’isspecifiedtwice,bothasatargetfor‘DELETE’andasaseparatesourcefordata因为同时进行查询和修改。
解决方法：查询语句外面再套一个查询形成一张临时表
原因：在MySQL中不允许在UPDATE或DELETE语句中使用子查询来操作同一个表
改为：
deletefrompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(peopleId)>1);上面这条语句在mysql中执行会报错，因为同时对表进行查询和更新，mysql不支持这种方式。oracel和msserver都支持这种方式。
解决方法：查询语句外面再套一个查询形成一张临时表删除user_region表中user_id重复的数据，只保留id最小的
deletefromuser_regionwhereuser_idin(selectuser_idfrom(selectuser_idfromuser_regionGROUPBYuser_idHAVINGcount(*)>1)asa)andidnotin(selectidfrom(selectmin(id)asidfromuser_regionGROUPBYuser_idhavingcount(*)>1)asb);问：为什么不能只用notinmin(id)来删除呢？答：因为这样会多删除，会把user_id不重复的也删掉，非常危险，使用notin的话必须加user_idin条件.
select*fromuser_employee_infowhereuser_idin(selectuser_idfrom(selectuser_idfromuser_employee_infogroupbyuser_idhavingcount(*)>1)asa)andupdate_timenotin(selectupdate_timefrom(selectmax(update_time)asupdate_timefromuser_employee_infogroupbyuser_idhavingcount(*)>1)asb);这个sql对于两条user_id相同且update_time相同的数据就删除不了。
例如user_address表中有user_id和地址类型type，一个user同一类型地址只能存一个，假如表上没有唯一约束造成了重复数据，删除重复数据，保留id最大的
内部流程：建立临时文件tmp_file，把表的B+树存储到临时文件中。若此时有对表的操作，则会记录在rowlog文件中。把数据从原表全部刷到临时文件后，此时临时文件的数据就跟原表的数据一致。最后用临时文件替换表A的数据文件。
UPDATET1,T2,[INNERJOIN|LEFTJOIN]T1ONT1.C1=T2.C1SETT1.C2=T2.C2,T2.C3=exprWHEREcondition当你希望更新一批值，且值是通过select条件查询出来时解决思路1：使用INNERJOIN(最简洁)
UPDATEAaINNERJOINBbONb.key_id=a.key_idSETa.val=2WHEREb.satisfy=1解决思路2：
UPDATEAa,(SELECTA.idfromALEFTJOINBONB.key_id=A.key_idWHEREB.satisfy=1)bSETa.val=2WHEREa.id=b.id根据无关表table2中的某些信息去更新table1表
例如：
当要更新的目标表同时也在查询条件中时，会报错：Tablenameisspecifiedtwice,bothasatargetfor‘UPDATE’andasaseparatesourcefordatainmysql解决方法是把作为条件的目标表查询为一个中间表。
UPDATEMYTABLESETCOL=COL+1WHEREIDIN(SELECTIDFROMMYTABLEWHEREOTHERCOL=0);DELETEFROMMYTABLEWHEREIDIN(SELECTIDFROMMYTABLEWHEREOTHERCOL=0);改为：
UPDATEMYTABLESETCOL=COL+1WHEREIDIN(SELECT*FROM(SELECTIDFROMMYTABLEWHEREOTHERCOL=0)ASTEMP);DELETEFROMMYTABLEWHEREIDIN(SELECT*FROM(SELECTIDFROMMYTABLEWHEREOTHERCOL=0)ASTEMP);例如：FROMmanagerASm2改为FROM(select*frommanager)ASm2
意思是每个派生出来的表（或者叫子查询）必须有一个自己的别名。一般是在多表查询或者子查询的时候会出现这个错误，因为在嵌套查询中，子查询的结果是作为一个派生表给上一级进行查询，所以子查询的结果必须有一个别名。
例如
SELECTidFROM(SELECTidFROMuser);会报错Everyderivedtablemusthaveitsownalias.改为
SELECTidFROM(SELECTidFROMuser)astemp;在子查询的后面增加一句astemp，相当于给子查询的结果集派生表取别名为temp，问题就解决了。
但是下面这条sql就不会报错
比如如下mybatissql语句：
那么有没有办法让mybatis的update操作的返回值是受影响的行数呢。因为我们业务逻辑中有时会根据这个返回值做业务判断。答案当然是有的。修改数据库链接配置为：增加了useAffectedRows字段信息。
JDBC默认返回的是符合的行数Rowsmatched，如果想返回修改过（Changed）的行数，需要使用useAffectedRows参数useAffectedRows的含义：是否用受影响的行数替代查找到的行数来返回数据
创建一个字典表，字段名使用了key,加反引号执行DDL的时候没问题
CREATETABLE`dict`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`key`varchar(255)NOTNULL,`value`textNOTNULL,`description`textDEFAULTNULL,`enabled`BOOLEANDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;但jpaautoddl时生成的sql字段名是不带反引号的，执行sql就会报错，keyvarchar(255)处会报错，和mysql创建索引的关键字KEY冲突了。
总结如下：PostgreSQL-yesMySQL-no;DDLcausesanimplicitcommitOracleDatabase11gRelease2andabove-bydefault,no,butanalternativecallededition-basedredefinitionexistsOlderversionsofOracle-no;DDLcausesanimplicitcommitSQLServer-yesSybaseAdaptiveServer-yesDB2-yesInformix-yesFirebird(Interbase)-yes
应当将DDL和DML语句以及DCL语句严格分开，避免事务被隐性“破坏”，导致误操作情况发生。
#禁用自动提交setautocommit=off;#创建tb1createtabletb1(idintauto_incrementprimarykey,c1int);#开始事务starttransaction;#插入数据insertintotb1(c1)select1;insertintotb1(c1)select2;insertintotb1(c1)select3;#创建tb2createtabletb2(idintauto_incrementprimarykey,c1int);执行完上述sql后，如果想回滚3条插入操作，会发现无法将这3条数据删除，因为createtabletb2这条DDL执行完后会自动提交，顺带也会把之前的DML提交。
当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。
MySQL各版本，对于DDL的处理方式是不同的，主要有三种：
CopyTable方式，这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。
处理过程：1、首先新建Temptable，表结构是ALTARTABLE新定义的结构2、然后把原表中数据导入到这个Temptable3、删除原表4、最后把临时表rename为原来的表名为了保持数据的一致性，中间复制数据（CopyTable）全程锁表只读，如果有写请求进来将无法提供服务，连接数爆张。
在MySQL5.1（带InnoDBPlugin）和5.5中，有个新特性叫FastIndexCreation(FIC)，就是在添加或者删除二级索引的时候，可以不用复制原表。引入FIC之后，创建二级索引时会对原表加上一个S锁，创建过程不需要重建表（no-rebuild）；删除InnoDB二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作，不能写入，但大大加快了修改索引的速度（不含主键索引，InnoDB的按主键聚簇存储特性决定了修改主键依然需要CopyTable）。
FIC只对索引的创建删除有效，MySQL5.6OnlineDDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。
Inplace方式，这是原生MySQL5.5，以及innodb_plugin中提供的方式。所谓Inplace也就是在原表上直接进行，不会拷贝临时表。相对于CopyTable方式，这比较高效率。原表同样可读的，但是不可写。
Online这是MySQL5.6以上版本中提供的方式。无论是CopyTable方式，还是Inplace方式，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式DDL.与以上两种方式相比，online方式支持DDL时不仅可以读，还可以写，对于dba来说，这是一个非常棒的改进。
从MySQL5.6开始，InnoDB存储引擎提供一种叫在线DDL(OnlineDDL)的DDL执行方式，允许Inplace更改表和并发DML操作。此功能的好处包括：
OnlineDDL基于MySQL5.5开始提供的**快速索引创建特性(fastindexcreation)**，快速索引创建特性可以在不拷贝表的情况下进行索引创建和删除。
OnlineDDL是默认开启的，无需执行任何特殊操作即可启用在线DDL.默认情况下，MySQL在允许的情况下执行操作，并尽可能少地锁定。
可以使用ALTERTABLE语句的LOCK和ALGORITHM子句控制DDL操作的各个方面。这些子句放在语句的末尾，用逗号分隔表和列。例如：
ALTERTABLEtbl_nameADDPRIMARYKEY(column),ALGORITHM=INPLACE,LOCK=NONE;LOCK子句可用于微调对表的并发访问程度。ALGORITHM子句主要用于性能比较，并且在遇到任何问题时作为旧表复制行为的后备。例如：
MySQL在线DDL分为INPLACE和COPY两种方式，通过在ALTER语句的ALGORITHM参数指定。
上面只是OnlineDDL内部的实现方式，此外还有LOCK选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认mysql尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。
创建或增加二级索引，删除索引，重命名索引都支持in-place的方式，均支持并发DML，但是不能重建表。其中，删除索引和重命名索引只修改元数据。
创建和增加二级索引
删除索引
DROPINDEXnameONtable;ALTERTABLEtbl_nameDROPINDEXname;在删除索引时，该表仍可用于读写操作。DROPINDEX语句仅在完成访问表的所有事务完成后才结束，因此索引的初始状态反映了表的最新内容。
看版本，MySQL5.6及以上的话，支持OnlineDDL操作，不会锁表。MySQL5.6以下版本，不支持OnlineDDL操作，会锁表
增加主键（原来无显式主键），删除后再增加主键，都支持in-place，重建表，可并发DML，并不仅仅只修改元数据。删除主键不支持并发DML和in-place，并不仅仅只修改元数据。
ALTERTABLEtbl_nameADDPRIMARYKEY(column),ALGORITHM=INPLACE,LOCK=NONE;修改主键需要重建表，因为innodb是按主键聚簇存储的，需要大量数据重组操作，需要复制表，因此，最好在创建表时定义主键，而不是稍后发出ALTERTABLE…ADDPRIMARYKEY如果你创建一个没有主键的表，InnoDB会为你选择一个，它可以是在NOTNULL列上定义的第一个UNIQUE键，或者是系统生成的键。为避免不确定性以及额外隐藏列的潜在空间要求，请将PRIMARYKEY子句指定为CREATETABLE语句的一部分。
主键修改过程：MySQL通过将原始表中的现有数据复制到具有所需索引结构的临时表来创建新的聚簇索引。将数据完全复制到临时表后，把原始表重命名为一个新的临时表，然后把刚才的临时表重命名为原始表名，然后删除原始表。
除了改变字段类型以外的字段操作，均支持并发DML。所有的操作都支持in-place的方式。
增加字段
ALTERTABLEtbl_nameADDCOLUMNcolumn_namecolumn_definition,ALGORITHM=INPLACE,LOCK=NONE;注意：添加自增列时不允许并发DML。尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发
删除字段
ALTERTABLEtbl_nameDROPCOLUMNcolumn_name,ALGORITHM=INPLACE,LOCK=NONE;删除列需要进行大量数据重组，是一项开销很大的操作。
不需要触发器支持的MySQL在线更改表结构的工具
所有在线表结构修改工具的操作方式都类似：创建与原表结构一致的临时表，该临时表已经是按要求修改后的表结构了，缓慢增量的从原表中复制数据，同时记录原表的更改(所有的INSERT,DELETE,UPDATE操作)并应用到临时表。当工具确认表数据已经同步完成，它会进行替换工作，将临时表更名为原表。
showindexfromtable_name;或showkeysfromtable_name;
Non_unique是否非唯一，0不是，1是Key_name索引的名称。Seq_in_index索引中的列序列号，从1开始。Column_name列名称。Collation列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。Cardinality索引中唯一值的数目的估计值。通过运行ANALYZETABLE或myisamchk-a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。Index_type用过的索引方法（BTREE,FULLTEXT,HASH,RTREE）。
ALTERTABLEtable_nameADDindexindex_name(column_name(prefix_length));altertablepage_viewaddindexpathname(`pathname`(255));MySQL不能在ORDERBY或GROUPBY中使用前缀索引，也不能把它们用作覆盖索引(CoveringIndex)
[42000][1071]Specifiedkeywastoolong;maxkeylengthis3072bytes
KEYkey是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primarykey,uniquekey,foreignkey等。
另外，在MySQL中，对于一个PrimaryKey的列，MySQL已经自动对其建立了UniqueIndex，无需重复再在上面建立索引了。
INDEXindex是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为。
CREATETABLE`orders`(`order_num`int(11)NOTNULLauto_increment,`order_date`datetimeNOTNULL,`cust_id`int(11)NOTNULL,PRIMARYKEY(`order_num`),KEY`fk_orders_customers`(`cust_id`),CONSTRAINT`fk_orders_customers`FOREIGNKEY(`cust_id`)REFERENCES`customers`(`cust_id`)ONDELETECASCADEONUPDATECASCADE)ENGINE=InnoDBAUTO_INCREMENT=10000DEFAULTCHARSET=utf8;创建表格后添加：
altertableusersAUTO_INCREMENT=10000;而且该语句也适用于修改现有表的id上，比如大批量删除数据后，想id从654321退回123456开始
复制表结构及数据到新表CREATETABLE新表SELECT*FROM旧表这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用deletefromnewtable;来删除。不过这种方法的一个最不好的地方就是新表中没有了旧表的primarykey、Extra（auto_increment）等属性。需要自己用altertable语句添加，而且容易搞错。
只复制表结构到新表CREATETABLE新表SELECT*FROM旧表WHERE1=2其中的where只要是个恒为false的表达式即可，此方法同样会丢失约束等额外信息
CREATETABLE新表LIKE旧表此方法会保留表的约束等所有额外信息，推荐使用
完整复制表的方法:先创建新表，保留约束等，在插入旧表的所有数据CREATETABLEtargetTableLIKEsourceTable;INSERTINTOtargetTableSELECT*FROMsourceTable;
使用Alter语法:
ALTERTABLEtable_nameADDINDEXindex_name(column_list)ALTERTABLEtable_nameADDUNIQUE[KEY|INDEX][index_name](column_list)ALTERTABLEtable_nameADDPRIMARYKEY[index_name](column_list)例如:
可在同一个ALTERTABLE语句中同时指定多个修改子句，例如添加多列，或者添加多列、删除多列、添加索引等组合在一起，都可以：
1、同时删除多列
altertableuserdropcolumnemail,dropcolumneducation,dropcolumnjob,dropcolumnmarriage,dropcolumnphone;2、列和索引一起删除：
altertableuserdropCOLUMNuser_uuid,dropCOLUMNcode,dropCOLUMNname,dropCOLUMNage,dropKEYuser_uuid;3、同时添加多列和索引
altertablepage_viewdefaultcharsetutf8;修改表默认字符集同时把所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
RENAMETABLEold_tableTOnew_table;等价于ALTERTABLEold_tableRENAMEnew_table;
Mysql没有直接更改数据库名称的命令。
RENAMEDATABASE这条命令在MySQL5.1.7中被加入，但很快就发现这条命令所带来的危险，于是在MySQL5.1.23中这条命令被移除。
可通过mysqldump导入导出来复制数据库，数据量大时谨慎使用在数据库外执行以下命令，将db_old改名为db_new
mysqladmin-uroot-ppswdcreatedb_newmysqldump-uroot-ppswddb_old|mysql-uroot-ppswddb_new之后选择删除db_old
唯一键约束，可以在一个列上添加约束，也可以在多个列上添加唯一约束。
在username列上添加唯一约束
CREATETABLE`t_user`(`Id`int(11)NOTNULLAUTO_INCREMENT,`username`varchar(18)NOTNULLunique,`password`varchar(18)NOTNULL,PRIMARYKEY(`Id`))ENGINE=InnoDBAUTO_INCREMENT=1018DEFAULTCHARSET=gbk;在多列上添加唯一约束
注意唯一约束用index关键字，因为唯一约束其实就是索引
可以在创建表时规定约束（通过CREATETABLE语句），或者在表创建之后也可以（通过ALTERTABLE语句）
下面是postgresql中的一个建表语句：
原因：数据库中已有一个同名约束，即约束的名字FKjui11rhjxr1u2hd6un2fdggkp重复了。MySQL约束名在整个数据库中必须是唯一的，只在一个表上是唯一的还不行。也就是说，假如同数据库的表tableA上有名字是constraintA的约束，那么tableB上再创建名为constraintA的约束就会失败。
在information_schema库的KEY_COLUMN_USAGE表中可以查看某个约束名在数据库上是否已经存在：
在MySQL3.23版本后，InnoDB引擎类型的表支持了外键约束，外键主要用于引用和参照完整性的约束检查。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的：
在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。
外键的定义语法：
一般来说，称被引用的表为父表，引用的表为子表。外键定义时ONDELETE和ONUPDATE表示在对父表进行DELETE或UPDATE操作时，对子表所做的操作，可定义的子表操作有：
CASCADE（跟随外键改动）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE和UPDATE操作。SETNULL（设空值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为NULL值，但子表对应的列必须允许为NULL。SETDEFAULT（设默认值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为默认值。NOACTION（无动作，默认的）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。RESTRICT（限制外表中的外键改动）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ONDELETE或ONUPDATE，RESTRICT就是默认的外键设置。
在其他数据库中，如Oracle数据库中，有一种称为延时检查（deferredcheck）的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查，因此从上面的定义可以看出，在MySQL数据库中NOACTION和RESTRICT的功能是相同的。
mysql>setforeign_key_checks=0;QueryOK,0rowsaffected(0.00sec)mysql>LOADDATA....mysql>setforeign_key_checks=1;QueryOK,0rowsaffected(0.00sec)对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是SELECT…LOCKINSHAREMODE方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞，如下：
先创建测试环境，如下：
#SessionAmysql>beginmysql>deletefromparentwheretag_id=3;#SessionBmysql>beginmysql>insertintochild(article_id,article_tag)values(1,3);#阻塞SessionB执行insert语句时被阻塞。当SessionA手动执行commit提交事务后，SessionB才能继续。
在上述的例子中，两个会话中的事务都没有进行COMMIT或ROLLBACK操作，而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁，而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁，这时INSERT的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时SessionB会读到父表有tag_id=3的记录，可以进行插入操作。但是如果会话A对事务提交了，则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。
在mysql的多个存储引擎中，innodb支持外键，但是由于外键，也会对innodb表增加锁定机制；
假设一个表为parent，一个表为child，child表有外键pidREFERENCESparent(id)；在一个session中setautocommit=0，执行对parent或child的操作，在另一个session执行对child或parent的操作，从而得出以下结论：
一、对父表的操作1、inserttoparent，新插入行的id值为XXXchild会锁外键值为XXX的行,不会锁其他行
2、updateparent，原id为XXX，现id为YYYchild会锁外键值为XXX,YYY的行,不会锁其他行
3、deletefromparent，删除行的id为XXXchild会锁外键值为XXX,不会锁其他行
二、对子表的操作1、inserttochild，插入行的外键值为XXXparent会锁值XXX的行,不会锁其他行
2、updatechild，更新行的外键值原为XXX，现为YYYparent会锁XXX,YYY行,但要注意:存在间隔锁,也会锁其他行(XXX,YYY之间的位置)
3、deletefromchild，删除行的外键值为XXXparent会锁XXX的行,但要注意:存在间隔锁,也会锁其他行(XXX-1的位置)