Fork me on GitHub
Fork me on GitHub

MySQL数据类型和sql_mode

发表于   |   更新于   |   分类于   |   阅读次数

DBA

开发DBA

数据库设计(E-R关系图)、SQL开发、内置函数、存储例程(存储过程和存储函数，存储过程没有返回值，函数有返回值)、触发器、事件调度器(event scheduler) 。

1.什么是mysql存储过程?
存储过程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令。
2.为什么要使用mysql存储过程？
应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询sql语句，当我们的应用程序很庞大很复杂的时候问题就出现这，不易维护！另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储过程正好可以帮我们解决这些问题。

  • 存储函数(stored function)。返回一个计算结果，该结果可以用在表达式里。
  • 存储过程(stored procedure)。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。
  • 触发器(trigger)。与数据表相关联，当那个数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。
  • 事件(event)。根据时间表在预定时刻自动执行。

管理DBA

安装、升级，备份、恢复，用户管理、权限管理，监控、分析、基准测试，语句优化(要会写SQL语句)，数据字典，按需要配置服务器（服务器变量：MyISAM, InnoDB, 缓存, 日志）
运维人员虽然不是开发SQL的，但是我们需要对别人写出来的SQL语句做性能评估，去分析他写的SQL是不是足够高效。如果不是，我们需要对对方的SQL语句做改写。

SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

MySQL数据类型

数据类型的作用

1、存储的值类型；比如如果定义成字符型，往里面存数值是不允许的。就算你存1，也是被存储为字符了。
2、占据的存储空间大小；
3、定长，变长；
4、如何被索引及排序；
5、是否能够被索引。有些数据类型不能被索引的。

数据字典

系统编目(system catalog)
保存数据库服务器上的元数据。可以把初始化后的mysql库理解为mysql的数据字典。

元数据：

  • 关系的名字 ——关系就是表
  • 每个关系的各字段的名字 ——字段就是属性
  • 各字段的数据类型和长度
  • 约束

  • 每个关系上的视图的名字及视图的定义

  • 授权用户的名字

  • 用户的授权和帐户信息 ——这些元数据是存储在user表

  • 统计类的数据： ——很重要，可以评估数据库性能

    • 每个关系字段的个数；
    • 每个关系中行数；
    • 每个关系的存储方法； ——表使用哪种存储引擎存储的

  • 保存元数据的数据库：

    • information_schema ——存储统计类元数据
    • mysql
    • performance_schema

performance_schema和information_schma一样，是两个虚库，有点类似于Linux的/proc文件系统，开机里面会有很多文件，但是一关机去看磁盘那个目录，里面没有任何数据。提供这两个接口就是为了兼容SQL语句的，能够实现对于整个数据库中任何信息的查找都能够基于SQL语句实现。所以mysql把这些信息虚拟成关系了。就像Linux一样，为什么提供/proc？/proc中存储的都是linux的内核参数，它把每个参数映射成一个文件中的数据。说白了就是把一个本来不是文件系统的东西映射成了一个文件系统，所以我们称/proc为伪文件系统。这样我们就可以操作文件系统的统一接口来操作内核。mysql也是同样的道理，本来获取mysql的这些数据需要通过其他方式，但是对于mysql的接口的操作方式绝大多数是通过SQL语句实现的。所以它也把这些信息抽象为关系的形式了。

MySQL数据类型

  • 字符型

    • char
    • varchar
    • binary
    • varbinary
    • text
    • blob

  • 数值型

    • 精确数值型
      • 整型
      • 十进制数据：decimal
    • 近似数值型
      • 单精度浮点型
      • 双精度浮点型

  • 日期时间型

    • 日期型 DATE
    • 时间型 TIME
    • 日期时间型 DATETIME
    • 时间戳 TIMESTAMP
    • YEAR

  • 布尔型：mysql没有真正意义上的布尔型，实际上使用tinyint来表示的，且只用1位表示。

  • 内建类型

    • ENUM
    • SET

数值型

  • TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT 这5种是整型
  • DECIMAL是十进制
  • FLOAT：单精度
  • DOUBLE：双精度
  • BIT：按位实现数据存取的，但是不建议使用。

字符型

  • CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，这几种不区分大小写
  • BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、ENUM、SET，这些都是二进制形式存取的，区分大小写，没有字符集

日期时间型

DATE、TIME、DATETIME、TIMESTAMP、YEAR
会涉及到很多转换：
1、字符串varchar/char转日期

select STR_TO_DATE('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')

2、日期转字符串

select DATE_FORMAT('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')

数据类型的修饰符

CHAR、VARCHAR和TEXT几种字符型常用的属性修饰符

  • NOT NULL：非空约束
  • NULL：允许为空
  • DEFAULT ‘string’：指定默认值，只适用于char和varchar，不适用于TEXT类型

  • CHARACTER SET ‘字符集’ #如果你不指定，默认从表继承，如果表没有，默认从库继承，如果库也没有，就从数据库服务器的全局继承。字符集对于我们将来存储、检索、比较等等是很关键的。

    mysql> SHOW VARIABLES LIKE ‘%char%’; #查看当前数据库服务器的默认字符集
    mysql> SHOW CHARACTER SET #查看支持的字符集

  • COLLATION ‘规则’: 排序规则。什么是排序规则呢？就算是gb2312的中文编码，你怎么对中文字符排序呢？谁应该大于谁，谁应该小于谁呢？我们有很多种方式，第一按拼音字母表，第二按照起笔(. — 丿)，第三按照笔画的多少排序等。所以同一种字符集内部要定义排序规则。

    mysql> SHOW COLLATION; #查看支持的排序规则。如果设定了字符集没有指定排序规则，每一种字符集都有其默认的排序规则。

【注意】:char是不需要字符结束符的，而varchar是有结束符的。char本身最多能存储255个字符，varchar最多能使用两个字节，65535个字符。而且能使用定长就不要使用变长，这跟我们在磁盘上快速查找记录有关系。变长的记录维护起来非常困难。无论是对插入、删除等对性能上都有负面影响。
另外要想存储大段文本，就要用text。但是不到万不得已的时候不要使用text，因为text本身索引起来很困难，并且text是作为对象存储的。什么对象存储？就是说不会直接存储到表中，而是存储在表之外的某个存储空间，而表中的那个字段存的仅仅是指向这个存储位置的指针。而且text不能使用全字段索引，但是char可以。

BINARY、VARBINARY和BLOB几种字符型常用的属性修饰符

默认是没有字符集的，因为它们是以二进制字节串的方式或者说是字节码的方式存储的。因此它们排序的时候是按照字节大小排序

  • NOT NULL
  • NULL
  • DEFAULT: 不适用于BLOB

整型的常用属性修饰符：

  • AUTO_INCREMENT：自动增长。前提：非空，且惟一；支持索引，非负值；
    【注意】:清空表后，重新插入数值，这个值继续增长，而不会从头开始计数。如果想把表中数据清空以后从头开始计数，alter表重置计数器，或者是清空表时用truncate，不用delete。
  • UNSIGNED：无符号，表示不支持负值。
  • NULL
  • NOT NULL
  • DEFAULT

浮点型常用修饰符

  • NOT NULL
  • NULL
  • DEFAULT
  • UNSIGNED

日期时间型的修饰符

  • NOT NULL
  • NULL
  • DEFAULT

ENUM和SET的修饰符

  • NOT NULL
  • NULL
  • DEFAULT ‘’

SQL_MODE

MySQL SQL_MODE

MySQL SQL_MODE: SQL模式。
SQL模式就是定义mysql的工作模式的。比如下面那个例子，超出了范围怎么办呢？是截取后发waringings还是直接拒绝插入呢？SQL模式还能定义mysql在某些特性场景下能够模拟其他关系型数据库的行为，比如mysql有自己的工作特性，但是在一定程度上可以模拟oracle的工作特性。

mysql> create table t2 (name CHAR(3));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES ('jerry');
ERROR 1406 (22001): Data too long for column 'name' at row 1

修改sql_mode是通过修改全局变量的值来实现的。
有这几种模式：TRADITIONAL, STRICT_TRANS_TABLES(只对支持事务的表有效), or STRICT_ALL_TABLES(对所有表都支持严格模式)。

mysql> SHOW GLOBAL VARIABLES like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

服务器变量

1.设定服务器变量的值：（仅用于支持动态的变量，比如dataDir改了也没用）

  • 支持修改的服务器变量：

    • 动态变量：可以MySQL运行时修改。sql_mode支持运行时修改。
    • 静态变量：于配置文件中修改其值，并重启后方能生效。

  • 服务器变量从其生效范围来讲，有两类：

    • 全局变量：服务器级别，修改之后仅对新建立的会话有效。
    • 会话变量：会话级别，仅对当前会话有效。
      会话建立时，从全局继承各变量。继承完了，每个会话都独立管理自己的会话变量。

2.查看服务器变量

mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];   默认是查SESSION的变量值
mysql> SELECT @@{GLOBAL|SESSION}.VARILABLE_NAME; 
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';      #information_schema这个库里有个表叫GLOBAL_VARIABLES。
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';


【示例】：

3.修改变量
前提：默认仅管理员有权限修改全局变量

mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';

【注意】:无论是全局还是会话级别的动态变量修改，在重启mysqld后都会失效；想永久有效，可定义在配置文件中的相应段中[mysqld]；另外，不是所有的服务器变量都可以写在配置文件中，绝大多数都可以。所以mysql服务器变量很独特，有3种生效方式：动态修改、定义配置文件或者执行命令时传递命令行参数。但这3者能使用的变量并不完全相同。有些变量可以作为命令行参数但是不能配置文件中。

查看mysql支持的命令行参数有哪些以及哪些可以写在配置文件中：

[root@console ~]# mysqld --help --verbose