eric0435
TA的个人档案
个人介绍
系统架构师(高级工程师),ACOUG&CSOUG核心成员，Oracle Young Expert 。政府信息化技术顾问。湖南省政府采购评审专家，从2007年开始从事社会保险系统开发，应用架构设计，数据库管理工作。擅长Oracle数据库故障诊断，性能调优。
擅长的技术
- Java
- Go
- 前端开发
- Linux
- 数据库
- 高分内容
- 最新动态
- 文章
- 问答
-
发表了文章 2020-02-17
PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040
PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040环境描述:oracle服务器端版本：oracle 12.2.0.1.0oracle客户端版本：oracle 11.2.0.1.0PLSQL是11.4 因为PLSQL连接数据库也是要通过Oracle客户端，那么使用11g客户端访问oracle 12c应该也会得到如下错误： C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:07 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR:ORA-28040: No matching authentication protocol查看关于错误的详细描述: [oracle@shard1 admin]$ oerr ora 2804028040, 0000, "No matching authentication protocol"// *Cause: There was no acceptable authentication protocol for// either client or server.// *Action: The administrator should set the values of the// SQLNET.ALLOWED_LOGON_VERSION_SERVER and// SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the// client and on the server, to values that match the minimum// version software supported in the system.// This error is also raised when the client is authenticating to// a user account which was created without a verifier suitable for// the client software version. In this situation, that account's// password must be reset, in order for the required verifier to// be generated and allow authentication to proceed successfully. 在服务器端查看表：dba_users，PASSWORD_VERSIONS 列是：11G 12C SQL> select username, account_status,password_versions from dba_users where account_status='OPEN'; USERNAME ACCOUNT_STATUS PASSWORD_VERSIONS------------------------------ ---------------------------------------------------------------- ----------------------------------SYS OPEN 11G 12CSYSTEM OPEN 11G 12CGSMCATUSER OPEN 11G 12CJY OPEN 11G 12CMYGDSADMIN OPEN 11G 12CAPP_SCHEMA OPEN 11G 12C 6 rows selected.服务器端：修改 sqlnet.ora 配置：（配置修改后，不需要重启oracle服务器）我这里是新创建的sqlnet.ora，因为原来没有创建 [oracle@shard1 admin]$ ls -lrt总用量 12-rw-r--r--. 1 oracle oinstall 1441 8月 28 2015 shrept.lstdrwxr-xr-x. 2 oracle oinstall 61 10月 12 2017 samples-rw-r-----. 1 oracle oinstall 960 1月 18 2018 tnsnames.ora-rw-r--r-- 1 oracle oinstall 504 2月 17 12:58 listener.ora[oracle@shard1 admin]$ vi sqlnet.oraSQLNET.ALLOWED_LOGON_VERSION_SERVER=11SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11客户端：再次尝试连接，提示用户名密码错误： C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:16:06 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR:ORA-01017: invalid username/password; logon denied 修改服务器端 sqlnet.ora 后，需要重新登录sqlplus，再修改用户密码，否则修改用户密码后，标记的密码版本仍然为11G 12C；重新登录sqlplus，修改scott用户密码，并查看 PASSWORD_VERSIONS，多了一个 10G [oracle@shard1 ~]$ sqlplus sys/abcd@shardcat as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 17 13:07:28 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter user sys identified by "abcd"; User altered. SQL> select username, account_status,password_versions from dba_users where account_status='OPEN'; USERNAME ACCOUNT_STATUS PASSWORD_VERSIONS------------------------------ ---------------------------------------------------------------- ----------------------------------SYS OPEN 10G 11G 12CSYSTEM OPEN 11G 12CGSMCATUSER OPEN 11G 12CJY OPEN 11G 12CMYGDSADMIN OPEN 11G 12CAPP_SCHEMA OPEN 11G 12C 6 rows selected.客户端：再次尝试登录oracle 12c，成功: C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:39 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>总结:oracle11g 客户端连接 oracle 12c服务器，需要在服务器端配置 sqlnet.ora，并重新修改用户密码。
-
发表了文章 2020-02-11
DM7 外部表
DM7 外部表需指定如下信息： 表名、表所属的模式名； 列定义； 控制文件路径。语法格式 CREATE EXTERNAL TABLE < 表名定义> < 表结构定义>;< 表名定义> ::=[< 模式名>.]< 表名>< 表结构定义> ::= (< 列定义> {,< 列定义>}) < 列定义> ::= < 列名> < 数据类型>FROM子句 = FROM子句1 | FROM子句2 | FROM子句3| FROM子句4FROM子句1 ::= FROM '< 控制文件路径>'FROM子句2 ::= FROM DATAFILE '< 数据文件路径>' [< 数据文件参数列表>]FROM子句3 ::= FROM < 控制文件目录> < 控制文件选项>FROM子句4 ::= FROM DATAFILE < 数据文件目录> < 数据文件选项> [< 数据文件参数列表>]< 数据文件参数列表> ::= PARMS(< 参数选项> {,< 参数选项>})< 参数选项> ：：= [FIELDS DELIMITED BY < 表达式>] | [RECORDS DELIMITED BY < 表达式>]|[ ERRORS ]|[BADFILE '< 数据文件路径>']| |［LOG < 日志文件路径>］|［NULL_STR ］|［SKIP < 跳过行数>］|［CHARACTER_CODE < 文件字符集>］< 控制文件目录> ::= DEFAULT DIRECTORY < 控制文件目录名>< 控制文件选项> ::= LOCATION ('< 控制文件名>')< 数据文件目录> ::= DEFAULT DIRECTORY < 数据文件目录名>< 数据文件选项> ::= LOCATION ('< 数据文件名>') 参数 < 模式名> 指明该表属于哪个模式，缺省为当前模式； < 表名> 指明被创建的外部基表名； < 列名> 指明基表中的列名； < 数据类型> 指明列的数据类型，暂不支持多媒体类型； < 控制文件路径> 指明使用的控制文件的路径的字符串； < 数据文件路径> 指明使用的数据文件路径的字符串； < 参数选项> FIELDS表示列分隔符；RECORDS表示行分隔符；ERRORS表示忽略外部表数据转换中出现错误的行数，取值范围为大于0的正整数，缺省为0，表示不忽略错误。此处ERRORS 和控制文件中的ERRORS写一个就行，如果都写，以控制文件中的为主；LOG表示日志文件路径， 默认日志文件名为fldr.log；NULL_STR指定数据文件中NULL值的表示字符串，默认忽略此参数；SKIP指定跳过数据文件起始的逻辑行数，默认为0；CHARACTER_CODE指定数据文件中数据的编码格式，默认为GBK，可选项有GBK，UTF-8，SINGLE_BYTE和EUC-KR； < 表达式> 字符串或十六进制串类型表达式，列分隔符只允许字符串类型； < 控制文件目录名> 指数据库对象目录的名称。图例 语句功能供DBA或具有CREATE_TABLE权限的用户定义外部基表。MPP环境下不支持创建外部表。使用说明 < 表名>指定了所要建立的外部基表名。如果< 模式名>缺省，则缺省为当前模式。表名需要是合法的标识符。且满足SQL语法要求； 外部表的表名最大长度为128个字符； 所建外部基表至少要包含一个< 列名>指定的列，在一个外部基表中，各< 列名>不得相同。一张外部基表中至多可以包含2048列； 外部基表不能存在大字段列； 外部基表不能存在任何约束条件； 外部基表不能为临时表，不能建立分区； 外部基表上不能建立任何索引； 外部基表是只读的，不存在表锁，不允许任何针对外部表的增删改数据操作，不允许truncate外部表操作； 控制文件路径，以及数据文件路径建议采用绝对路径； 控制文件的格式为： [OPTIONS(errors=)]LOAD [DATA]INFILE [LIST] |INTO TABLE tablenameFIELDS 其中：OPTIONS选项：可选部分，目前OPTIONS中仅支持errors选项，用于指定忽略数据转换出现错误的行数；此处errors 和< 参数选项>中的errors写一个就行，如果都写，以此处控制文件中的为主。：指明使用的数据文件列表；tablename：指明表名，创建外部表时，表名可以与此不同；：指明同一行中各个列的分隔符； 数据文件中一行数据必须以回车结束； 外部表支持查询ROWID、USER和UID伪列，不支持查询TRXID伪列。 举例说明例1 指定操作系统的一个文本文件作为数据文件，编写控制文件及建表语句。数据文件（/home/dmdba/data.txt），数据如下： a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15控制文件（/home/dmdba/ctrl.txt）如下： load datainfile '/home/dmdba/data.txt'into table extfields '|'建表： SQL> create external table ext (2 l_char char(1),3 l_character character(3),4 l_varchar varchar(20),5 l_numeric numeric(6,2),6 l_decimal decimal(6,2),7 l_dec dec(6,2),8 l_money decimal(19,4),9 l_bit bit,10 l_bool bit,11 l_boolean bit,12 l_integer integer,13 l_int int,14 l_bigint bigint,15 l_tinyint tinyint,16 l_byte byte,17 l_smallint smallint,18 l_binary binary,19 l_varbinary varbinary,20 l_float float,21 l_double double,22 l_real real,23 l_date date,24 l_time time,25 l_timestamp timestamp,26 l_interval interval year27 )from '/home/dmdba/ctrl.txt';executed successfullyused time: 62.123(ms). Execute id is 312.系统执行建表语句后，就在数据库中建立了相应的外部基表。查询ext表： SQL> select * from ext; LINEID L_CHAR L_CHARACTER L_VARCHAR L_NUMERIC L_DECIMAL L_DEC L_MONEY L_BIT L_BOOL L_BOOLEAN L_INTEGER L_INT L_BIGINT L_TINYINT L_BYTE L_SMALLINT L_BINARY L_VARBINARY L_FLOAT L_DOUBLE L_REAL L_DATE L_TIME L_TIMESTAMP L_INTERVAL ---------- ------ ----------- ------------ --------- --------- ----- ------- ----- ------ --------- ----------- ----------- -------------------- ----------- ----------- ----------- -------- ----------- ------------------------- ------------------------- ------------------ ---------- --------------- --------------------------- ---------------------1 a abc varchar_data 12.34 12.34 12.34 12.3400 0 1 1 1234 1234 1234 100 11 1234 0x01 0x01 1.420000000000000E+01 1.210000000000000E+01 1.2100000E+01 1999-10-01 09:10:21 2002-12-12 00:00:00.000000 INTERVAL '15' YEAR(2) used time: 7.248(ms). Execute id is 314.例 2 指定操作系统的一个文本文件作为数据文件（/home/dmdba/data1.txt），数据如下： 10|9|74|3|2建表: SQL> create external table ext_2(c1 int,c2 int,c3 int) from datafile '/home/dmdba/data1.txt' parms(fields delimited by '|');executed successfullyused time: 11.008(ms). Execute id is 331.查询结果： SQL> select * from ext_2; LINEID C1 C2 C3 ---------- ----------- ----------- -----------1 10 9 72 4 3 2 used time: 8.222(ms). Execute id is 332.
-
发表了文章 2020-02-11
DM7 AWR报告
DM有类似于Oracle的AWR报告，这里将给大家介绍如何生成数据库快照以及AWR报告。本文演示环境：DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT,启用DM快照需要调用DBMS_WORKLOAD_REPOSITORY包(DM MPP环境下不支持DBMS_WORKLOAD_REPOSITORY包)1.创建DBMS_WORKLOAD_REPOSITORY系统包。 SQL> call SP_INIT_AWR_SYS(1);DMSQL executed successfullyused time: 647.666(ms). Execute id is 254.2.启用状态检测。 SQL> SELECT SF_CHECK_AWR_SYS; LINEID SF_CHECK_AWR_SYS---------- ----------------1 1 used time: 5.590(ms). Execute id is 255.3.下面语句设置间隔为60分钟，也可以是其他值： SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60);DMSQL executed successfullyused time: 18.797(ms). Execute id is 256.4.手动创建快照： SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();DMSQL executed successfullyused time: 61.528(ms). Execute id is 258.等待几分钟后再次执行 SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();DMSQL executed successfullyused time: 49.152(ms). Execute id is 259.这里我们可以多执行几遍试试，创建几个不同的快照。 5.查看创建的快照信息，包括快照id： SQL> SELECT * FROM SYS.WRM$_SNAPSHOT; LINEID SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL ---------- ----------- ----------- --------------- --------------------------- --------------------------- --------------------------- -----------1 1 NULL 1 2020-02-04 18:25:32.000000 NULL 2020-02-04 18:28:35.586240 12 2 NULL 1 2020-02-04 18:25:32.000000 NULL 2020-02-04 18:31:21.207610 1如果此时我们想知道，对应各个时点数据库的运行状况，我们可以利用DBMS_WORKLOAD_REPOSITORY包中的AWR_REPORT_HTML方法来生成HTML格式的AWR报表。6.生成 snapshot 的 id 在 1-2 范围内的 AWR 分析报告的带 html 格式的内容。 SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));7.把snapshot的id在1-2范围内的AWR分析报告生成到/home/dmdba目录下的awr1_2.html文件内。 SQL> call sys.awr_report_html(1,2,'/home/dmdba','awr1_2.html');DMSQL executed successfullyused time: 317.395(ms). Execute id is 260.生成的报表格式如下 从AWR报告来说，熟悉Oracle的朋友基本上可以无缝衔接到DM数据库
-
发表了文章 2020-02-11
MySQL Strict SQL MODE
严格SQL模式控制MySQL如何处理数据改变语句(insert或update)中的无效或缺失值。一个值可能由于各种原因而无效。例如，它对于列来说有错误的数据类型，或者超过了列的范围。当新记录被插入而对于非NULL且没有显式在定义时指定DEFAULT子句的列没有包含值就会出现缺失值的情况。(对于一个NULL列，如果缺失值就会插入NULL值)严格SQL模式也会影响DDL语句比如create table。 如果严格SQL模式没有生效，MySQL对于无效或者缺失值会插入调整值并生成一个警告。在严格SQL模式中，可以通过使用insert ignore或udpate ignore来产生这种行为。 对于select这样不改变数据的语句，在严格SQL模式中无效值会生成一个警告而不是错误。 严格SQL模式对于试图创建一个键值而超过列的最大键值长度时会产生一个错误。当严格SQL模式没有启用时，会产生一个警告并且截断键值的长度使其满足最大键值长度。 严格SQL模式不影响是否对外键约束执行检查。foreign_key_checks可以被使用。 如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用严格SQL模式就会生效，但这些模式的影响会有不同:.对于事务表来说，当STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用后当在数据出现无效或丢失值就会出现错误。语句就会被终止与回滚。 .对于非事务表，如果在插入或更新语句中第一行记录出现坏值这些模式的行为是一样的:语句被终止并且表仍然保持不变。如果语句插入或修改多行记录并且在第二行或之后的行记录中出现坏值，那么结果依赖于严格SQL模式是否被启用。-对于STRICT_ALL_TABLES，MySQL会返回一个错误并忽略剩余的行记录。然而，因为早些的行记录已经被插入或被更新，会导致部分更新。为了避免这个问题，使用单行语句，就会终止而不会改变表数据。 -对于STRICT_TRANS_TABLES，MySQL会将一个无效的值转换成一个最接近的有效值并插入这个调整值。如果这个值将丢失，MySQL插入这个隐式缺省值。在这种情况下，MySQL生成一个敬告而不是一个错误并继续处理语句。 严格SQL模式对除零，零日期和日期中出现零的处理如下:.严格SQL模式影响对除零的处理，它包括MOD(N,0):对于数据改变操作(insert,update):-如果严格SQL模式没有被启用，除零会插入NULL并生成一个警告。-如果严格SQL模式被启用，除非指定了ignore否则除零操作会生成一个错误。对于insert ignore和update ignore操作，除零操作会插入NULL并生成一个警告。 对于select，除零操作会返回NULL。启用严格SQL模式会导致一个警告。.严格SQL模式会影响服务器是否允许'0000-00-00'为一个有效日期:-如果严格SQL模式没有被启用，'0000-00-00'被允许并且插入操作不会产生警告。-如果严格SQL模式被启用,'0000-00-00'不被允许并且插入操作会产生错误，除非你指定ignore。对于insert ignore或update ignore，'0000-00-00'被允许并且插入操作会产生警告。 .严格SQL模式影响服务器是否允许在日期中的年部分为非零但月和日部分允许为零(比如'2010-00-01'或'2010-01-00'):-如果严格SQL模式没有被启用，有零的日期被允许并且插入操作不产生警告。-如果严格SQL模式被启用,有零的日期不被允许并且插入操作产生错误，除非指定ignore。对于insert ignore或update ignore来说，有零的日期将以'0000-00-00'形式被插入并产生一个警告。 IGNORE关键字与严格SQL模式的对比这里将介绍在语句执行时IGNORE关键字(它降级错误为警告)和严格SQL模式(它升级警告为错误)的对比。描述它们影响那些语句以及应用它们有那些错误。 IGNORE对语句执行的影响MySQL中的一些语句支持可选的IGNORE关键字。此关键字将导致服务器降级某些类型的错误并生成警告。对于多行语句，IGNORE会导致语句跳到下一行，而不是中止。 例如，如果表t2有一个主键列i,试图在多行记录中插入相同的i值正常来说会产生一个重复键错误: mysql> insert into t2(i) values(1),(1);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'当使用IGNORE关键字时，包含重复键值的记录仍然不会被插入，但会使用警告来代替错误: mysql> insert ignore into t2(i) values(1),(1);Query OK, 1 row affected, 1 warning (0.15 sec)Records: 2 Duplicates: 1 Warnings: 1 mysql> show warnings; Level Code Message Warning 1062 Duplicate entry '1' for key 'PRIMARY' Warning 1062 Duplicate entry '1' for key 'PRIMARY' 2 rows in set (0.00 sec)以下语句支持IGNORE关键字:.create table... select:ignore不能应用到语句的create table或select部分但对于由select语句所提供记录来执行插入语句可以应用。对于唯一键值重复的记录会被丢弃。 .delete:ignore会导致MySQL在处理删除记录时忽略错误。 .insert:使用ignore，对于唯一键值重复的记录会被丢弃。对于重复键值的行记录会导致数据转换为最接近的有效值被插入。 对于分区表当没有匹配指定值的分区被找到时，ignore会导致包含那些不匹配值的记录的插入操作失败。.load data,load xml:使用ignore，对于唯一键值重复的记录会被丢弃。 .update:使用ignore，对于在唯一键值出现重复键值冲突的记录不会被更新。被更新的记录可能导致数据转换为最接近的有效值被插入。 ignore关键字应用到以下错误:ER_BAD_NULL_ERRORER_DUP_ENTRYER_DUP_ENTRY_WITH_KEY_NAMEER_DUP_KEYER_NO_PARTITION_FOR_GIVEN_VALUEER_NO_PARTITION_FOR_GIVEN_VALUE_SILENTER_NO_REFERENCED_ROW_2ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SETER_ROW_IS_REFERENCED_2ER_SUBQUERY_NO_1_ROWER_VIEW_CHECK_FAILED 严格SQL模式对语句执行的影响MySQL服务器可以以不同的SQL模式进行操作并且可以应用这些不同模式到不同的客房端，这依赖于sql_mode系统变量。在严格SQL模式中，服务器会将特定的警告升级成错误。 例如，在非严格SQL模式中，向整数类型列插入字符串'abc'的结果是将这个字符串值转换为0并生成一个警告: mysql> SET sql_mode = '';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t2(i) values('abc');Query OK, 1 row affected, 1 warning (0.02 sec) mysql> show warnings; Level Code Message Warning 1366 Incorrect integer value: 'abc' for column 'i' at row 1 1 row in set (0.00 sec)在严格SQL模式下，无效值会被拒绝并生成错误: mysql> SET sql_mode = 'STRICT_ALL_TABLES';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t2(i) values('abc');ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1在某些条件下，某些值可能超出范围或将无效行插入或从表中删除，严格SQL模式适用于以下语句:alter tablecreate tablecreate table ... selectdelete(单表和多表)insertload dataload xmlselect sleep()update(单表和多表)在存储程序中，如果程序是在严格模式生效时定义的，则刚才列出的类型的各个语句将以严格SQL模式执行。 严格SQL模式应用于以下错误，代表输入值可能无效或丢失这类错误。如果对于列值使用了错误数据类型或超过了值的范围那么值就是无效的。如果被插入的新行不包含NOT NULL列值但除了在列定义时显式指定了DEFAULT子句的那么就是值丢失。ER_BAD_NULL_ERRORER_CUT_VALUE_GROUP_CONCATER_DATA_TOO_LONGER_DATETIME_FUNCTION_OVERFLOWER_DIVISION_BY_ZEROER_INVALID_ARGUMENT_FOR_LOGARITHMER_NO_DEFAULT_FOR_FIELDER_NO_DEFAULT_FOR_VIEW_FIELDER_TOO_LONG_KEYER_TRUNCATED_WRONG_VALUEER_TRUNCATED_WRONG_VALUE_FOR_FIELDER_WARN_DATA_OUT_OF_RANGEER_WARN_NULL_TO_NOTNULLER_WARN_TOO_FEW_RECORDSER_WRONG_ARGUMENTSER_WRONG_VALUE_FOR_TYPEWARN_DATA_TRUNCATED
-
发表了文章 2020-02-11
MySQL SQL模式
MySQL服务器可以以不同的SQL模式来进行操作，并且依赖于sql_mode系统变量的值对不同的客户端可以应用这些不同的SQL模式。DBA可以设置全局SQL模式来匹配服务器操作要求，并且每种应用程序可以设置它的会话SQL模式来满足它的要求。 SQL模式会影响MySQL支持的SQL语法和数据验证检查。这可以在不同环境中让MySQL与其它数据库一起使用变得更容易。 当使用InnoDB表时，可以考虑使用innodb_strict_mode系统变量，它可以对InnoDB表启用额外的错误检查。 设置SQL模式在MySQL 5.7中缺省的SQL模式包含:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,和 NO_ENGINE_SUBSTITUTION。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。 为了在服务器启动时设置SQL模式，可以在命令行中使用--sql-mode="modes"选项或在选项文件比如Unix中的my.cnf或Windows上的my.ini文件中使用sql-mode="modes"选项。modes是用逗号分的不同模式列表。为了显式的清除SQL模式，可以在命令行中使用--sql-mode=""选项将SQL模式设置为空字符串，或者在选项文件中使用sql-mode=""。 MySQL安装程序可以在安装过程中会配置SQL模式。例如，mysql_install_db将在基本的安装目录中创建一个命名为my.cnf的缺省选项文件。这个文件包含设置SQL模式的记录。 如果SQL模式不同于缺省SQL模式或你所期待的SQL模式，可以检查服务器在启动时所读取的选项文件。 为了在运行时改变SQL模式，可以使用set语句来设置全局或会话级的sql_mode系统变量:set global sql_mode='modes';set session sql_mode='modes'; 设置global变量需要有super权限并且影响所有连接的客户端操作。设置session变量只影响当前客户端。每个客户端可以在任何时间改变它会话的sql_mode值来达到改变SQL模式的目的。 为了判断当前全局或session级的sql_mode值，执行以下命令: mysql> SELECT @@GLOBAL.sql_mode; @@GLOBAL.sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec) mysql> SELECT @@SESSION.sql_mode; @@SESSION.sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec)注意在创建分区表并插入数据之后改变服务器的SQL模式会对表的行为造成重大改变并且可能导致丢失或损坏数据。所以强烈建议在创建用户定义的分区表之后不要修改SQL模式。 当复制分区表时，在主从服务器之间不同的SQL模式也会导致一些问题。所以最好的结果就是在主从服务器上使用相同的SQL模式。 最重要的SQL模式MySQL中最重要的sql_mode值可能是这些:.ANSI这种SQL模式改变语法和行为使其更接近标准SQL。它是一种特定的组合模式列表。 .STRICT_TRANS_TABLES如果一个值不能以指定的方式插入到一个事务表，终止这个语句。对于非事务表，如果这个值在一个单行记录语句或在多行记录语句第一个出现时终止语句。从MySQL 5.7.5开始缺省的SQL模式包括STRICT_TRANS_TABLES。 .TRADITIONAL使用MySQL行为像一个传统的SQL数据库系统。简单来说这种模式对于将一个不正确值插入到一个列中时抛出一个错误来代替一个警告。它是特定组合模式中的一种。insert或update一旦发现错误会立即终止。如果您正在使用非事务性存储引擎，这可能不是您想要的，因为在错误之前所做的数据更改可能无法回滚，从而导致“部分完成”的更新。 当提到"strict mode"严格模式时，它意味着是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一种或两种都被启用。 完整的SQL模式列表下面是所有支持的SQL模式列表:.ALLOW_INVALID_DATES不对日期执行完全检查。只对月份的范围从1到12和日期的范围从1到31执行检查。这对于Web应用程序非常方便，您可以在三个不同的字段中获得年、月和日，并且希望准确地存储用户插入的内容(不执行日期校验)。这种SQL模式应用于date和datetime列。它不应用于timestamp列，因为它总是请求一个合法的日期值。 这种SQL模式要求月分与每天的取值是合法值，并且范围分别不能超过1到12和1到31。当严格模式被禁用时，一个无效的日期值比如'2014-04-31'将被转换成'0000-00-00'并且生成一个警告。当使用严格模式时，一个无效的日期值会生成一个错误。为了允许这样的日期值，启用ALLOW_INVALID_DATES模式。 .ANSI_QUOTES将"作为标识引用字符(像引用字符)而不是字符串引用字符。当使用这种SQL模式时仍然可以使用作为引用标识符，当ANSI_QUOTES被使用时，不能使用双引号来引用文本字符串，因为它被解析作标识符了。 .ERROR_FOR_DIVISION_BY_ZEROERROR_FOR_DIVISION_BY_ZERO模式影响对除以零的处理，这包含MOD(N,0)。对于数据修改操作(insert,update)，它的影响也依赖于是否启用了严格SQL模式。-如果这种模式没有被启用，除以零会插入NULL值并且没有警告。-如果这种模式被启用，除以零会插入NULL值并生成警告。-如果这种模式与严格SQL模式被启用，除以堆生成一个错误，除非IGNORE也被指定。对于insert ignore和update ignore，除以零会插入NULL值并生成警告。 对于select语句，除以零会返回NULL值。启用ERROR_FOR_DIVISION_BY_ZERO不管是否启用严格SQL模式会导致生成一个警告。 在MySQL 5.7.4中，ERROR_FOR_DIVISION_BY_ZERO被丢弃，在MySQL 5.7.4到5.7.7中当显式命名时ERROR_FOR_DIVISION_BY_ZERO不会做任何事。代替的是，它的影响被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式命名时ERROR_FOR_DIVISION_BY_ZERO会有影响并且没有包含在严格SQL模式中，就像MySQL5.7.4之前的版本一样。然而缺省情况下当严格模式启用下应该与它联合使用。如果ERROR_FOR_DIVISION_BY_ZERO被启用而没有启用严格模式或者当启用严格模式而没有启用ERROR_FOR_DIVISION_BY_ZERO时会出现这个警告。 因为ERROR_FOR_DIVISION_BY_ZERO被丢弃，它将在将来的版本中被删除并作为一个单独的模式名并且它的影响被包含在严格SQL模式。 .HIGH_NOT_PRECEDENCENOT操作的优先级就像NOT a between b and c会被解析成NOT (a between b and c)一样。在一些旧版本的MySQL中，表达式会被解析成(NOT a) between b and c。旧版本更高优先级行为可以通过启用HIGH_NOT_PRECEDENCE模式来获得。 mysql> SET sql_mode = '';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT NOT 1 BETWEEN -5 AND 5; NOT 1 BETWEEN -5 AND 5 0 1 row in set (0.00 sec) mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';Query OK, 0 rows affected (0.00 sec) mysql> SELECT NOT 1 BETWEEN -5 AND 5; NOT 1 BETWEEN -5 AND 5 1 1 row in set (0.00 sec).IGNORE_SPACE允许在函数名与(符号之间存在空格。这会造成内置函数名被作为保留关键字对待。因此标识符与函数名相同必须有引号。例如，因为有一个count()函数，因此在下面的语句中使用count作为表名就会出错: mysql> CREATE TABLE count (i INT);Query OK, 0 rows affected (0.13 sec) mysql> drop table count cascade;Query OK, 0 rows affected (0.09 sec) mysql> SET sql_mode = 'IGNORE_SPACE';Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE count (i INT);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1 mysql> create table count (i INT);Query OK, 0 rows affected (0.16 sec)IGNORE_SPACE模式应用到内置函数，而不是用户定义的函数或存储过程。它总是允许在用户定义的函数或存储过程名后有空格而不管是否启用了IGNORE_SAPCE模式。 .NO_AUTO_CREATE_USER除非指定了身份验证信息，否则将阻止GRANT语句自动创建新用户帐户。这个语句必须使用identified by来指定非空密码或使用identified with来使用一种验证插件。 最好使用create user来创建MySQL账号，然后使用Grant语句。NO_AUTO_CREATE_USER已经被丢弃并且缺省的SQL模式包含了NO_AUTO_CREATE_USER模式。将sql_mode修改为NO_AUTO_CREATE_USER模式会生成一个警告，除了指定sql_mode为DEFAULT.NO_AUTO_CREATE_USER将会在将来的版本中被删除，到时它的影响将会一直被启用。 之前，在NO_AUTO_CREATE_USER被丢弃之前，一个不启用它的原因是它是不安全的复制。现在它可以被启用并且使用create user if not exists,drop user if exists和alter user if exists而不是grant语句来执行安全复制管理。这些语句当从服务器相对于主服务器有不同的授权时可以启用安全复制。 .NO_AUTO_VALUE_ON_ZERONO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。正常来说通过向访列插入NULL或0来为列生成下一个序列值。NO_AUTO_VALUE_ON_ZERO会抑制0的行为因此只有插入NULL时会生成下一个序列值。如果0已经被存储到表的AUTO_INCREMENT列中这个SQL模式可能是有用的。(存储0是不建议的)例如，如果使用mysqldump来dump表并且然后再加载它，当遇到值0时MySQL正常来说会生成一个新的序列值，因此表的内容不同于被dump的内容。在加载dump文件之前启用NO_AUTO_VALUE_ON_ZERO来解决这个问题。mysqldump现在在它的输出中自动包含一个语句来启用NO_AUTO_VALUE_ON_ZERO来避免这个问题。 .NO_BACKSLASH_ESCAPES禁用在字符串中使用反斜杠字符()作为转义字符。启用此模式后，反斜杠将成为与其他字符一样的普通字符。 .NO_DIR_IN_CREATE创建表时，忽略所有索引目录和数据目录指令。此选项在从复制服务器上非常有用。 .NO_ENGINE_SUBSTITUTION当一个语句比如create table或alter table指定一个存储引擎已经被禁用或没有被编译时控制缺省存储引擎的自动替换。缺省的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因为存储引擎可以在运行时被附加进来，不可以存储引擎也以相同方式被对待:当NO_ENGINE_SUBSTITUTION被禁用，对于create table的缺省存储引擎被使用并且如果期待的存储引擎不可用会出现一个警告。对于alter table，会出现一个警告并且表不能被修改。当NO_ENGINE_SUBSTITUTION被启用时，如果期待的存储引擎不可用会出现一个警告并且表不会被创建或被修改。 .NO_FIELD_OPTIONS在show create table输出中不打印特定MySQL列选项。这种SQL模式被mysqldump以可移植模式来使用。 .NO_KEY_OPTIONS在show create table输出中不打印特定MySQL索引选项。这种SQL模式被mysqldump以可移植模式来使用。 .NO_TABLE_OPTIONS在show create table输出中不打印特定MySQL表选项(比如ENGINE)。这种SQL模式被mysqldump以可移植模式来使用。 .NO_UNSIGNED_SUBTRACTION两个整数相减，这里一种类型UNSIGNED，缺省情况下生成一个没有符号的结果。如果结果出现负数将会出现错误: mysql> SET sql_mode = '';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT CAST(0 AS UNSIGNED) - 1;ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'如果NO_UNSIGNED_SUBTRACTION模式被启用，结果将是负数: mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';Query OK, 0 rows affected (0.00 sec) mysql> SELECT CAST(0 AS UNSIGNED) - 1; CAST(0 AS UNSIGNED) - 1 -1 1 row in set (0.00 sec)如果使用此类操作的结果更新无符号整数列，则将该结果裁剪为该列类型的最大值，如果启用no_unsigned_subtract，则将其裁剪为0。如果严格SQL模式被启用，则会出现错误并且列会保持不变。 当no_unsigned_subtraction被启用时，就算任何操作数据是无符号的相减的结果是有符号的。例如比较表t1中的c2列与表t2中的c2列: mysql> SET sql_mode='';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);Query OK, 0 rows affected (0.20 sec) mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; Field Type Null Key Default Extra c2 bigint(21) unsigned NO 0 1 row in set (0.00 sec) mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;Query OK, 0 rows affected (0.25 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; Field Type Null Key Default Extra c2 bigint(21) NO 0 1 row in set (0.00 sec)这意味着bigint unsigned在所有上下文中不是100%可以使用。 .NO_ZERO_DATENO_ZERO_DATE模式影响服务器是否允许'0000-00-00'作为一种有效的日期。它的影响也依赖于是否启用了严格SQL模式。-如果模式没有被启用，'0000-00-00'被允许并且插入不会产生警告。-如果模式被启用，'0000-00-00'被允许并且插入会产生警告。-如果模式和严格SQL模式被启用，'0000-00-00'不被允许并且插入会出现错误，除非指定IGNORE选项。对于insert ignore和update ignore来说，'0000-00-00'被允许并且插入会产生警告。 在MySQL 5.7.4中，NO_ZERO_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。 因为NO_ZERO_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。 .NO_ZERO_IN_DATENO_ZERO_IN_DATE模式影响服务器是否允许日期中的年部分为非0但月或日部分为0。(这种模式影响日期比如'2010-00-01'或'2010-01-00'，而不是'0000-00-00'。为了控制服务器是否允许'0000-00-00'，使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影响也依赖于是否启用了严格SQL模式。-如果这种模式没有启用，有为0部分折日期被允许并且插入不产生警告。-如果这种模式被启用，有为0部分的日期值将以'0000-00-00'格式被插入并且生成一个警告。-如果这种模式与严格SQL模式被启用，有为0部分的日期不被允许并且插入会产生一个错误，除非你指定ignore。对于insert ignore和update ignore来说，有为0部分的日期将以'0000-00-00'格式被插入并且生成一个警告。 在MySQL 5.7.4中，NO_ZERO_IN_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_IN_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_IN_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。 因为NO_ZERO_IN_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。 .ONLY_FULL_GROUP_BY拒绝那些select list、HAVING condition或ORDER BY list引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列(由GROUP BY列唯一确定)。 从MySQL 5.7.5开始，缺省的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前，MySQL没有检测到功能依赖并且缺省情况下ONLY_FULL_GROUP_BY模式没有启用。 MySQL扩展了标准SQL来允许在having子句中引用select列表中的别名表达式。在MySQL 5.7.5之前，启用ONLY_FULL_GROUP_BY模式会禁用这种扩展，因此要求having子句以非别名表达式来书写。从MySQL5.7.5开始，这种限制被取消了因此having子句可以引用别名而不用管ONLY_FULL_GROUP_BY模式是否启用了。 .PAD_CAHR_TO_FULL_LENGTH默认情况下，在检索时从CHAR列值中裁剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH被启用，裁剪不会发生并且在检索CHAR列值时填充到它的完整长度。这种模式不应用于varchar列。检索时为其保留尾随空格。 mysql> CREATE TABLE t1 (c1 CHAR(10));Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO t1 (c1) VALUES('xy');Query OK, 1 row affected (0.03 sec) mysql> SET sql_mode = '';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1; c1 CHAR_LENGTH(c1) xy 2 1 row in set (0.00 sec) mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec) mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1; c1 CHAR_LENGTH(c1) xy 10 1 row in set (0.00 sec).PIPES_AS_CONCAT将||作为字符串连接操作符(与concat()一样)而不是作为OR的同义词。 .REAL_AS_FLOAT将REAL作为FLOAT的同义词。缺省情况下，MySQL将REAL作为DOUBLE的同义词。 .STRICT_ALL_TABLES对所有的存储引擎启用严格SQL模式。无效的数据会被拒绝。从MySQL 5.7.4到5.7.7，STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。 .STRICT_TRANS_TABLES对事务型存储引擎启用严格SQL模式并且在可能的情况下使用非事务型存储引擎。从MySQL 5.7.4到5.7.7，STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。
滑动查看更多
暂无更多信息
-
发表了文章
2020-02-17
PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040
-
发表了文章
2020-02-11
DM7 外部表
-
发表了文章
2020-02-11
DM7 AWR报告
-
发表了文章
2020-02-11
MySQL Strict SQL MODE
-
发表了文章
2020-02-11
MySQL SQL模式
-
发表了文章
2019-12-20
DM7数据复制之数据库级复制
-
发表了文章
2019-12-20
DM7数据复制之表级复制
-
发表了文章
2019-12-12
Oracle 11G RAC One Node's Instance Arise ORA-01105 ORA-01606
-
发表了文章
2019-12-12
mysqld --skip-grant-tables
-
发表了文章
2019-12-12
DM并行查询
-
发表了文章
2019-10-30
ORA-00600 qosdExpStatRead expcnt mismatch
-
发表了文章
2019-10-30
PostgreSQL 行安全策略
-
发表了文章
2019-10-30
PostgreSQL使用表继承实现分区表
-
发表了文章
2016-05-16
Linux 格式化分区 报错Could not stat /dev/sda3 --- No such file or directory
-
发表了文章
2016-05-16
Linux 11G RAC启用HugePages与AMM的禁用
-
发表了文章
2016-05-16
位图索引
-
发表了文章
2016-05-16
使用rman增量备份来更新传输表空间
-
发表了文章
2016-05-16
使用rman增量备份来更新传输表空间
-
发表了文章
2016-05-16
Oracle Linux 7配置vncserver
-
发表了文章
2016-05-16
Weblogic BEA-141281 unable to get file lock, will retry 故障处理
滑动查看更多
暂无更多信息