详解mysql数据库sql_mode模式

mysql可以运行在不同sql mode模式下面,sql mode模式定义了mysql应该支持的sql语法,数据校验等!

查看默认的sql mode模式:
select @@sql_mode;
我的数据库是:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
在此模式下面,如果插入的数据的长度大于定义的长度,那么就会报错!

set session sql_mode=’REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI’;
在这种模式下面:插入的数据的长度大于定义的时候,就会截取,并警告,但是可以插入进去
session表示只在本次中有效
global:表示在本次连接中不生效,而对于新的连接就生效

启用NO_BACKSLASH_ESCAPES模式,使反斜线成为普通字符,在导入数据时候,如果数据中有反斜线,启用这个模式是个不错的选择

启用PIPES_AS_CNCAT模式,将||看成是普通字符串

常用的sql mode:
sql mode值 说明
ANSI: ‘REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE和ANSI组合’,这种模式使语法和行为更符合标准的sql
STRICT_TRANS_TABLES : 使用与事务和非事务表,严格模式
TRADITIONAL :也是严格模式,对于插入不正确的值给出错误而不是警告。用在事务时,只要发生错误就立即回滚

在Mysql5.0以下,默认的sql mode(sql mode参数)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在这些模式下可以插入超过字段定义长度的数据,或是在字段中没有定义的元素数据(如,enum)。不过在插入后会有一个warning(可以用 show warnings来查看)。
在Mysql5.0以上版本中,有三种sql mode模式(ANSI、TRADITIONAL和STRICT_TRANS_TABLES(严格模式))可以用来解决以下问题:
(1). 通过设置不同的sql mode,可以在不同严格程序进行数据校验,有效地保证了数据准确性.
(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务修改太多.
通过设置sql mode为STRICT_TRANS_TABLES(严格模式)来实现数据的严格校检,使错误数据不能插入,从而保证数据准确性。TRADITIONAL 模式也属于严格模式,同样可以实现严格校检,使错误数据不能插入,从而保证数据准确性。不过在这种模式MAX(X,0)返回的结果是NULL,所以在包含有MAX的运算中根据实际情况设定好sql mode.
ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。在某些情况下,ENUM值也可以为空字符串(”)或NULL:如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值。该字符串与“普通” 空字符串不同,该字符串有数值值0。
如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素。
(1)sql mode为ANSI

mysql> create table test(id bigint(20) auto_increment primary key, browsertype enum('ie','firefox','other')); 
mysql> insert into test(browsertype) values('ie') ; 
Query OK, 1 row affected (0.07 sec) 
mysql> insert into test(browsertype) values('maxthon') ; 
Query OK, 1 row affected (0.03 sec) 
mysql> show warnings;(数据虽然成功insert,但有warning) 
+---------+------+--------------------------------------------------+ 
| Level | Code | Message | 
+---------+------+--------------------------------------------------+ 
| Warning | 1265 | Data truncated for column 'browsertype' at row 1 | 
+---------+------+--------------------------------------------------+ 
1 row in set (0.01 sec) 
mysql> select * from test; 
+----+-------------+ 
| id | browsertype | 
+----+-------------+ 
| 1 | ie | 
| 2 | | 
+----+-------------+ 
2 rows in set (0.01 sec)

(2)使用严格模式(STRICT_TRANS_TABLES)

mysql> set session sql_mode='STRICT_TRANS_TABLES'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> select @@sql_mode; 
+---------------------+ 
| @@sql_mode | 
+---------------------+ 
| STRICT_TRANS_TABLES | 
+---------------------+ 
1 row in set (0.00 sec) 
mysql> insert into test(browsertype) values('maxthon') ; 
ERROR 1265: Data truncated for column 'browsertype' at row 1 

mysql> insert into test(browsertype) values('firefox') ; 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test; 
+----+-------------+ 
| id | browsertype | 
+----+-------------+ 
| 1 | ie | 
| 2 | | 
| 3 | firefox | 
+----+-------------+ 
3 rows in set (0.00 sec)

(3) TRADITIONAL模式

mysql> create table t11 (i int); 
Query OK, 0 rows affected (0.02 sec) 
mysql> set sql_mode='ANSI'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into t11 values(9%0); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from t11; 
+------+ 
| i | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.00 sec) 
mysql> set sql_mode='TRADITIONAL'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into t11 values(9%0); 
ERROR 1365: Division by 0 
mysql> show warnings; 
+-------+------+---------------+ 
| Level | Code | Message | 
+-------+------+---------------+ 
| Error | 1365 | Division by 0 | 
+-------+------+---------------+ 
1 row in set (0.02 sec)

 

正则中的一些特殊字母

i 匹配大小写

s 模式中的圆点元字符(.)匹配所有的字符,包括换行符

x 模式中的空白字符除了被转义的或在字符类中的以外完全被忽略,在未转义的字符类之外的 # 以及下一个换行符之间的所有字符,包括两 头,也都被忽略

A (PCRE_ANCHORED) 如果设定了此修正符,模式被强制为“anchored”,即强制仅从目标字符串的开头开始匹配即自动在模式开头加上^。

D (PCRE_DOLLAR_ENDONLY) 如果设定了此修正符,模式中的美元元字符仅匹配目标字符串的结尾。没有此选项时,如果最后一个字符是换行符的话,美元符号也会匹配此字符之前(但不会匹配 任何其它换行符之前)。如果设定了 m 修正符则忽略此选项。Perl 中没有与其等价的修正符。  S 当一个模式将被使用若干次时,为加速匹配起见值得先对其进行分析。如果设定了此修正符则会进行额外的分析。目前,分析一个模式仅对没有单一固定起始字符的 non-anchored 模式有用。

U (PCRE_UNGREEDY) 本修正符反转了匹配数量的值使其不是默认的重复,而变成在后面跟上“?”才变得重复。这和 Perl 不兼容。也可以通过在模式之中设定 (?U) 修正符来启用此选项。

X (PCRE_EXTRA) 此 修正符启用了一个 PCRE 中与 Perl 不兼容的额外功能。模式中的任何反斜线后面跟上一个没有特殊意义的字母导致一个错误,从而保留此组合以备将 来扩充。默认情况下,和 Perl 一样,一个反斜线后面跟一个没有特殊意义的字母被当成该字母本身。当前没有其它特性受此修正符控制。即:贪婪模式,最 大限度匹配 如:/a[\w]+?e/U匹配abceadeddd中的abceade而不是abce,如果不加U修正,则匹配abce  u (PCRE_UTF8) 此修正符启用了一个 PCRE 中与 Perl 不兼容的额外功能。模式字符串被当成 UTF-8。本修正符在 Unix 下自 PHP 4.1.0 起可用,在 win32 下自 PHP 4.2.3 起可用。

PV、UV、IP都代表什么

在网络营销实践中经常会用到网站统计工具,这里边的PV、UV、IP值都代表什么呢,下面做详细的说明。
先说下PV、UV、IP的定义:
PV(访问量):Page View, 即页面浏览量或点击量,用户每次刷新即被计算一次。
UV(独立访客):Unique Visitor,访问您网站的一台电脑客户端为一个访客。00:00-24:00内相同的客户端只会被计算一次。
IP(独立IP):指独立IP数。00:00-24:00内相同IP地址之被计算一次。
再谈下PV、UV、IP的区别:
IP(独立IP)表示,拥有特定唯一IP地址的计算机访问您的网站的次数,因为这种统计方式比较容易实现,具有较高的真实性,所以成为大多数机构衡量网站流量的 重要指标。比方你是ADSL拨号上网的,你拨一次号都自动分配一个IP,这样你进入了本站,那就算一个IP,当你断线了而没清理Cookies,之后又拨 了一次号,又自动分配到一个IP,你再进来了本站,那么又统计到一个IP,这时统计数据里IP就显示统计了2次。但是UV(独立访客)没有变,因为两次都 是你进入了本站。
来说明一下PV高不一定代表来访者多;PV与来访者的数量成正比,但是PV并不直接决定页面的真实来访者数量。比如一个网站就你一个人进来,通过不断的刷新页面,也可以制造出非常高的PV。
再来说说UV:IP在这里是指公用的广域网传输协议族(Tcp/Ip)为每一台处在因特网上的计算机(可以是个人电脑、服务器以及其他兼容广域网传输 协议族规定的 接入设备)都定义了四个段落(例如:192.168.0.255形式,有时会加入第五段落端口号作为描述信息,端口号是介于1-65535 之间的数字)共 32位长度二进制代码的标识,叫IP协议地址,简称IP地址,俗称IP,它是一个一台连接着广域网的计算机区别于其他机器的标识,一般情 况下,它在同一级别的网络(例如某个局域网、社区网、教学楼网或者INTERNET)范围内是唯一的。
UV是指不同的、通过互联网访问、浏览一个网页的自然人。
比如,在一台电脑上,哥哥打开了微软的官方主页,注册了一个会员。弟弟一会儿也看了看,注册了另一个会员。由于兄弟两个使用的是相同的计算机,那么他 们的 IP是一样的,微软的官方计数器记录到一个IP登陆的信息。但是,具有统计功能的统计系统,可以根据其他条件判断出实际使用的用户数量,返回给网站 建设者真实、可信和准确的信息。比如通过注册的用户,甚至可以区分出网吧、机房等共享一个IP地址的不同计算机。上面的例子就说明虽然是同一IP,但是有 2个独立访客。再举个例子吧,比如一个网吧里,有100个人都进入了我的网站,但是一个网吧对外都是一个IP的,所以统计系统只统计到一个IP;但是因为 网吧里有100人在访问我的站,尽管他们都仅仅打开我的网站的首页,或者这100人都把我网站所有页面都看过了一遍,统计系统都只统计到100个独立访 客。
使用独立用户作为统计量有什么好处?它比IP更加准确吗?
IP是一个反映网络虚拟地址对象的概念,UV是一个反映实际使用者的概念,每个UV相对于每个IP更加准确地对应一个实际的浏览者。
综上所述:使用UV作为统计量,可以更加准确的了解单位时间内实际上有多少个访问者来到了相应的页面。

mysql中order by field的使用

SELECT * FROM fruit 
ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+
SELECT * FROM fruit
ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+
SELECT * FROM fruit
ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
+----------+--------+---------------------+