MySQL所支持的数据类型与表字段约束类型的学习教程

MySQL 数据（字段）类型
在创建表的时候，要明确定义字段对应的数据类型。MySQL 主要的数据类型分为数值类型、字符串（文本）类型、时间日期类型和其他类型几类。
数值类型
数值类型说明：

补充说明
在 int（integer） 系列中，只能存储整型值，且可以在后面用括号指定显示的尺寸（M），如果不指定则会默认分配。如果实际值的显示宽度大于设定值，将会显示实际值而不会截断以适应显示尺寸。如 smallint(3) 中的 3 即为显示尺寸，即显示三位的数值（不包括 - 号）
int 类型可以指定 UNSIGNED 属性，即无符号（非负），所以存储范围有两种
在 float、double 及 decimal 类型中，不能指定 UNSIGNED 属性，其显示尺寸包含了小数点精度（D），即 float(3,1) 保存范围为 -99.9 至 99.9
decimal 必须指定显示尺寸（M）和小数点精度（D），float 和 double 都是可选的
在可能涵盖取值范围的基础上，尽可能选择较小的类型以提高效率和节约存储空间，如年龄，就选择 tinyint(3) 。该原则对于字符类型同样适用
字符串（文本）类型
字符串（文本）类型说明：

补充说明
char 和 varcha 需要指定长度，不同的是，char 存储时总是按照指定的长度储存，而 varchar 则根据实际字符串长度再加上一个字节分配空间。
时间日期类型
时间日期类型说明：

提示
在 PHP 中，一般情况下对于时间都是按照 UNIX 时间戳以 int 类型存储于表中，再根据实际需要用 PHP 的时间函数进行处理，但不完全都是这样。

MySQL 表字段属性
主键
表的主键（primary key，主关键字）是表中的一个或多个字段，它的值用于惟一地标识表中的某一条记录。一个表不能有多个主关键字，并且主关键字的列不能包含空值和重复值。主关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。
语法：
PRIMARY KEY (column_name)

在这个表中，uid 这个字段就是该表的主键。
建立主键应该遵循的一般原则
主键应当是对用户没有意义的
永远也不要更新主键，如果主键需要更新，则说明主键应对用户无意义的原则被违反了
主键不应包含动态变化的数据，如时间戳、创建时间等
主键应当由系统自动生成
外键
在两个表的关系中，当一张表（如表A ）的主关键字被包含在另一张表（如表B）中时，A 表中的主关键字便成为 B 表的外键（外关键字）。B 表称为主表，A 表称为从表。
外键主要用于保持数据一致性，完整性，避免冗余数据，使两张或多张表形成关联。

在这个文章评论表 comment 样例中，uid 便是外键，它是 user 表的主键。在该表中记录了用户对文章的评论，关于用户信息，只需对应 user 表的主键 uid 即可。在数据一致性方面，比如当 comment 表没有 uid 为 3 的评论后，才可以删除 user 表中 uid = 3 的记录。
自动递增
在 MySQL 数据库中，提供了字段的自动递增（AUTO_INCREMENT）属性，即将该字段设置为 int 类的数据类型，每向数据表添加一条记录，该字段的值会自动加1 。设置了自动递增后，该列不用再设置默认值和唯一性约束。
例子：

uid mediumint(8) NOT NULL auto_increment

非空
因为逻辑上的一些要求，有时候需要把字段属性设置为非空（NOT NULL），如记录用户名、密码等非空值的字段。
设置为非空的列，虽然不是必须，但最好设定一个默认值，以防止意外的错误和减少增加数据时的 SQL 语句复杂度。当向数据表增加数据记录时，如果设置为非空的字段不写入数据，系统将会以默认值写入。
例子：

username char(20) NOT NULL default ''
//将NOT NULL 改为 NULL
ALTER TABLE user CHANGE username username char(20) NULL

提示
在上面的例子中 CHANGE 后面 username username ，前者表示更改的字段名，后者表示新字段名。在本例中，只更改了字段属性为 NULL ，不涉及到字段名称的更改，因此二者一致。
空
与非空相反，可以将字段属性设置为空，如果字段属性没有设置为非空（NOT NULL）属性，那么系统默认就是可以为空（NULL）值的。
将NULL 改为 NOT NULL：

ALTER TABLE user CHANGE username username char(20) NOT NULL DEFAULT ''

唯一性约束
有时候某些字段数据不允许重复，如用户名，这时候就需要增加唯一性约束（UNIQUE）。
语法：

UNIQUE (column_name)
//给表字段增加 UNIQUE
ALTER TABLE tb_name ADD UNIQUE (column_name)
//去掉 UNIQUE
ALTER TABLE tb_name DROP INDEX column_name

主键（PRIMARY KEY）会强制性的拥有自动定义的唯一性约束，不用额外定义 UNIQUE 。
索引
数据库索引（index）是为了增加查询速度而对字段附加的一种标识。我们对表的某些可能需要经常查询的字段建立适当的索引，那么在查询该字段数据时，便会显著的加快查询速度。
语法：
KEY key_name (column_name)
KEY 后面的字符为索引名称，括号内为建立索引的字段名。
改进后的建表 SQL

CREATE TABLE user (
  uid mediumint(8) unsigned NOT NULL auto_increment,
  username char(15) NOT NULL default '',
  password char(32) NOT NULL default '',
  email varchar(40) NOT NULL default '',
  regdate int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (uid),
  UNIQUE KEY username (username),
  KEY email (email)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

补充说明
在建表语句正文后面还附加了建表的其它几个属性：
ENGINE：表示存储引擎类型，分为 MyISAM 和 InnoDB 两种类型。MyISAM 不支持事务处理等高级处理，强调表的性能，执行也比 InnoDB 快。而 InnoDB 提供事务支持已经外部键等高级数据库功能，性能上比 MyISAM 差。默认为 MyISAM 类型
CHARSET：表示数据表表字符集，一般为 gbk 或 utf8 以及 big5 等，为了兼容性考虑，我们设置为 utf8 字符集
AUTO_INCREMENT：设置主键默认增长的开始数

赞 (0)

相关推荐

  • php MySQL与分页效率

    最基本的分页方式: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ... 在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引: 举例来说,如果实际SQL类似下面语句,那么在category_id, id两列上建立复合索引比较好: SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10 子查询的分页方式: 随着数据量的增加,页数会

  • mysql分页原理和高效率的mysql分页查询语句

    以前我在mysql中分页都是用的 limit 100000,20这样的方式,我相信你也是吧,但是要提高效率,让分页的代码效率更高一些,更快一些,那我们又该怎么做呢? 第一部分:看一下分页的基本原理: 复制代码 代码如下: mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20***************** 1. row **************id: 1select_type: SIMPLEtable: m

  • Mysql中分页查询的两个解决方法比较

    Mysql中分页查询的两个解决方法比较

    mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下 复制代码 代码如下: SELECT COUNT(*) FROM foo WHERE b = 1; SELECT a FROM foo WHERE b = 1 LIMIT 100,10; 另外一种是使用SQL_CALC_FOUND_ROWS 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10; SELECT FOUND_

  • php mysql数据库操作分页类

    复制代码 代码如下: <?php /*  *    mysql数据库 分页类  *    @package    pagelist  *    @author        yytcpt(无影)  *    @version    2008-03-27  *    @copyrigth    http://www.d5s.cn/   */ /*  *    分页样式     .page{float: left;font: 11px Arial, Helvetica, sans-serif; pa

  • mysql把主键定义为自动增长标识符类型

    1.把主键定义为自动增长标识符类型 在mysql中,如果把表的主键设为auto_increment类型,数据库就会自动为主键赋值.例如: create table customers(id int auto_increment primary key notnull, name varchar(15)); insert into customers(name) values("name1"),("name2"); 一旦把id设为auto_increment类型,my

  • mysql 分页优化解析

    如果你的数据量有几十万条,用户又搜索一些很通俗的词,然后要依次读最后几页重温旧梦.mysql该很悲壮的不停操作硬盘. 所以,可以试着让mysql也存储分页,当然要程序配合.(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页:用存储过程+游标方式分页,具体实现原理不是很清楚,设想如果用一次查询就得到需要的结果,或者是id集,需要后续页时只要按照结果中的I

  • oracle,mysql,SqlServer三种数据库的分页查询的实例

    MySql: MySQL数据库实现分页比较简单,提供了 LIMIT函数.一般只需要直接写到sql语句后面就行了.LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数.例如:select * from table WHERE - LIMIT 10; #返回前10行select * from table WHERE - LIMIT 0,10; #返回前

  • MySQL学习笔记之数据定义表约束,分页方法总结

    本文实例讲述了MySQL学习笔记之数据定义表约束,分页方法.分享给大家供大家参考,具体如下: 1. primary key 主键 特点:主键是用于唯一标识一条记录的约束,一张表最多只能有一个主键,不能为空也不能重复 create table user1(id int primary key,name varchar(32)); mysql> insert into user1 values(1,'hb'); Query OK, 1 row affected (0.10 sec) mysql>

  • Mysql的游标的定义使用及关闭深入分析

    Mysql从5.0开始支持存储过程和trigger,给我们喜欢用mysql的朋友们更喜欢mysql的理由了,语法上和PL/SQL有差别,不过搞过编程的人都知道,语法不是问题,关键是思想,大致了解语法后,就从变量定义,循环,判断,游标,异常处理这个几个方面详细学习了.关于游标的用法Mysql现在提供的还很特别,虽然使用起来没有PL/SQL那么顺手,不过使用上大致上还是一样, 定义游标 declare fetchSeqCursor cursor for select seqname, value f

  • mysql limit分页优化方法分享

    同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,明显要好于直接使用limit.这

  • MySQL 添加、修改、删除表的列及约束等表的定义

    ALTER TABLE:添加,修改,删除表的列,约束等表的定义. 查看列:desc 表名; 修改表名:alter table t_book rename to bbb; 添加列:alter table 表名 add column 列名 varchar(30); 删除列:alter table 表名 drop column 列名; 修改列名MySQL: alter table bbb change nnnnn hh int; 修改列名SQLServer:exec sp_rename't_stude

  • mysql 存储过程中变量的定义与赋值操作

    一.变量的定义 mysql中变量定义用declare来定义一局部变量,该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头,并且是在其它语句之前,也可以同时申明多个变量,如果需要,可以使用default赋默认值. 定义一个变量语法如下: declare var_name[,...] type[default value]看一个变量定义实例 declare last date;二.mysql存储过程变量赋值 变量的赋值可直接赋值与查询赋值来操作,直接赋值可以用set

  • 浅谈mysql 自定义函数

    因为工作需要,要写一个mysql的自定义行数,如下 DELIMITER $$ DROP FUNCTION IF EXISTS `onlineFunction`$$ CREATE FUNCTION `onlineFunction`(rrrr VARCHAR(50)) RETURNS VARCHAR(255) BEGIN IF(rrrr='online') THEN RETURN '上线';END IF; END$$ DELIMITER ; 第一行DELIMITER 定义一个结束标识符,因为MySQ

  • MySQL 百万级分页优化(Mysql千万级快速分页)

    以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

  • MySQL 自定义函数CREATE FUNCTION示例

    mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION myFunction -> (in_string VARCHAR(255), -> in_find_str VARCHAR(20), -> in_repl_str VARCHAR(20)) -> -> RETURNS VARCHAR(255) -> BEGIN -> DECLARE l_new_string VARCHAR(25

随机推荐