MySQL数据类型及sql模型
存储引擎,也被称为表类型:
MyISAM表: 无事务,表锁
.frm: 表结构定义文件
.MYD: 表数据
.MYI: 索引
InnoDB表:事务,行锁
.frm: 表结构
.ibd: 表空间(数据和索引)
所有表共享一个表空间文件;
建议:每表一个独立的表空间文件;(修改配置文件在[mysqld]项下加入innodb_file_per_table = 1)
MySQL:
在默认的mysql数据库中默认的存储引擎为MyISAM
其他新建的数据库的存储引擎取决于默认的存储引擎设定值
SHOW ENGINES
SHOW TABLE STATUS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
程序语言连接数据的方式:
动态SQL:通过函数或方法与数据库服务建立连接,
嵌入式SQL:
客户端组件:mysql、mysqladmin、mysqldump、mysqlimport、mysqlcheck
服务器组件:mysqld, mysqld_safe, mysqld_multi
my.cnf
/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somefile --> ~/.my.cnf
[mysqld]
[mysqld_safe]
[client]
host =
[mysql]
绝大数配置选项都能在命令行下直接指定
# mysqld --help --verbose
在指定datadir = /mydata/data后,在指定的目录下有名为hostname.err: 错误日志
MySQL启动失败的原因:
1、此前服务未关闭;
2、数据初始化失败;
3、数据目录位置错误;
4、数据目录权限问题;
DBA:
开发DBA:数据库设计、SQL语句、存储过程、存储函数、触发器
管理DBA:安装、升级、备份、恢复、用户管理、权限管理、监控、性能分析、基准测试
数据类型:
数值型
精确数值
int
decimal
近似数值
float
double
real
字符型
定长:CHAR(#)、BINARY
变长:VARCHAR(#)、VARBINARY
text, blob
ENUM, SET
日期时间型
date, time, datetime, timestamp
数据类型:
1、存入的值类型;
2、占据的存储空间;
3、定长还变长;
4、如何比较及排序;
5、是否能够索引;
数据类型总结:
整型:
类型 字节 最小值 最大值
TINYINT 1 有符号-128,无符号0 有符号127,无符号255
SMALLINT 2 有符号-32768,无符号0 有符号32767,无符号65535
MEDIUMINT 3 有符号-8388608,无符号0 有符号8388607,无符号1677215
INT和INTEGER 4 有符号-2147483648,无符号0 有符号2147483647,无符号4294967295
BIGINT 8 有符号-9223372036854775808,无符号0 有符号9223372036854775807,无符号18446744073709551615
浮点型:
类型 字节 最小值 最大值
FLOAT 4
DOUBLE 8
说明:当需要精确到小数点后10位以上,使用DOUBLE类型
定点数:
类型 字节 最小值 最大值
DEC(M,D)和DECIMAL(M,D) M+2
说明:有效取值范围由M和D确定,浮点型存储的是近似值,定点数型存储的是字符串
位类型:
类型 字节 最小值 最大值
BIT(M) 1-8 BIT(1) BIT(64)
说明:字节数是M,取值范围为1-8
日期时间型:
类型 字节 最小值 最大值
DATE 4 1000-01-01 9999-12-31
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 4 19700101080001 2038年某个时刻
TIME 3 -835:59:59 838:59:59
YEAR 1 1901 2155
字符串类型:
CHAR系列:
类型 字节 描述
CHAR(M) M M为0-255的整数
VARCHAR(M) M M为0-65535的整数
说明:VARCHAR长度可变
TEXT类型:
类型 字节 描述
TINYTEXT 0-255 值的长度为+2个字节
TEXT 0-65535 值的长度为+2个字节
MEDIUMTEXT 0-167772150 值的长度为+3个字节
LOANGTEXT 0-4294967295 值的长度为+4个字节
说明:用于存储大量字符串
BINARY系列:
类型 字节 描述
BINARY(M) M 允许长度为0-M
VARBINARY(M) M 允许长度为0-M
说明:存储二进制数据,VARBINARY长度可变
BLOB系列:
类型 字节
TINYBLOB 0-255
BLOB 0-2^16
MEDIUBLOB 0-2^24
LOANGBLOB 0-2^32
说明:存储大量的二进制数据
mysql> SHOW CHARACTER SET; 查看当前服务器支持的所有字符集
mysql> SHOW COLLATION; 查看当前服务器支持的所有排序规则
修饰符:
AUTO_INCREMENT 自动增长(对于整型)默认从一开始,步长为一。不能为空
AUTO_INCREMENT
整型
非空
无符号(只能为正数)UNSIGNED
主键或惟一键
mysql> SELECT LAST_INSERT_ID();显示上一次自动增长数值
SQL模型
常用:
ANSI QUOTES -—A double quote (") is now treated as an identifier quote (`).
Strings can only be quoted with a single quote (’)
IGNORE_SPACE—-This sql_mode allows a space between a built-in function and the open parenthesis
STRICT_ALL_TABLES—-When this sql_mode is not set, invalid data values are allowed,
usually converted to valid data by using zero values or truncating the value.
When this sql_mode is set, invalid data values are rejected, and an error is thrown.
STRICT_TRANS_TABLES -- invalid data values in a transactional table are rejected, and an error is thrown
TRADITIONAL—-Syntax and behavior closely match traditional expectations
查询sql模型:
mysql>show global variables like 'sql_mode';
MySQL服务器变量
按作用域,分为两类:
全局变量
SHOW GLOBAL VARIABLES
会话变量
SHOW [SESSION] VARIABLES
按生效时间,分为两类:
动态:可即时修改
静态:
写在配置文件中
通过参数传递给mysqld(或mysqld_safe)进程
动态调整参数的生效方式:
全局:对当前会话无效,只对新建立会话有效;
会话:即时生效,但只对当前会话有效;
服务器变量:@@变量名
显示:SELECT @@global.sql_mode
设定:SET GLOBAL|SESSION 变量名='value'