I want to restrict the value of a field in a row of a table to a specific range. Is it possible to restrict my relationship_level field to [0.00 to 1.00]?

At the moment I am using DECIMAL(2,2), it wouldn't allow DECIMAL(1,2) as M must be >= D. I assume a data type of DECIMAL(2,2) will actually allow values from 00.00 up to 99.99?

CREATE TABLE relationships (

from_user_id MEDIUMINT UNSIGNED NOT NULL,

to_user_id MEDIUMINT UNSIGNED NOT NULL,

relationship_level DECIMAL(2,2) UNSIGNED NOT NULL,

PRIMARY KEY (from_user_id, to_user_id),

FOREIGN KEY (from_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,

FOREIGN KEY (to_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,

INDEX relationship_from_to (to_user_id, from_user_id, relationship_level)

) ENGINE = INNODB;

Is there a better way to do this, can anyone foresee any limitations?

Many thanks!

解决方案

You can simulate a check constraint in MySQL using triggers.

For example, if you want to force all values larger than 1.00 to be stored as 1.00, you could do so with 2 triggers like this:

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_ins_relationships $$

CREATE TRIGGER tr_b_ins_relationships BEFORE INSERT ON relationships FOR EACH ROW BEGIN

IF new.relationship_level > 1

THEN

SET new.relationship_level = 1;

END IF;

END $$

DELIMITER ;

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_upd_relationships $$

CREATE TRIGGER tr_b_upd_relationships BEFORE UPDATE ON relationships FOR EACH ROW BEGIN

IF new.relationship_level > 1

THEN

SET new.relationship_level = 1;

END IF;

END $$

DELIMITER ;

MySQL中各种字段的取值范围(转)[@more@]TINYINT

-128 - 127

TINYINT UNSIGNED

0 - 255

SMALLINT

-32768 - 32767

SMALLINT UNSIGNED

0 - 65535

MEDIUMINT

-8388608 - 8388607

MEDIUMINT UNSIGNED

0 - 16777215

INT 或 INTEGER

-2147483648 - 2147483647

INT UNSIGNED 或 INTEGER UNSIGNED

0 - 4294967295

BIGINT

-9223372036854775808 - 9223372036854775807

BIGINT UNSIGNED

0 - 18446744073709551615

FLOAT

-3.402823466E+38 - -1.175494351E-38

0

1.175494351E-38 - 3.402823466E+38

DOUBLE 或 DOUBLE PRECISION 或 REAL

-1.7976931348623157E+308 - -2.2250738585072014E-308

0

2.2250738585072014E-308 - 1.7976931348623157E+308

DECIMAL[(M,[D])] 或 NUMERIC(M,D)

由M(整个数字的长度,包括小数点,小数点左边的位数,小数点右边的位数,但不包括负号)和

D(小数点右边的位数)来决定,M缺省为10,D缺省为0

DATE

1000-01-01 - 9999-12-31

DATETIME

1000-01-01 00:00:00 - 9999-12-31 23:59:59

TIMESTAMP

1970-01-01 00:00:00 - 2037年的某天(具体是哪天我也不知道,呵呵)

TIME

-838:59:59' to 838:59:59

YEAR[(2|4)]

缺省为4位格式,4位格式取值范围为1901 - 2155,0000,2位格式取值范围为70-69(1970-2069)

CHAR(M) [BINARY] 或 NCHAR(M) [BINARY]

M的范围为1 - 255,如果没有BINARY项,则不分大小写,NCHAR表示使用缺省的字符集.在数据库

中以空格补足,但在取出来时末尾的空格将自动去掉.

[NATIONAL] VARCHAR(M) [BINARY]

M的范围为1 - 255.在数据库中末尾的空格将自动去掉.

TINYBLOB 或 TINYTEXT

255(2^8-1)个字符

BLOB 或 TEXT

65535(2^16-1)个字符

MEDIUMBLOB 或 MEDIUMTEXT

16777215 (2^24-1)个字符

LONGBLOB 或 LONGTEXT

4294967295 (2^32-1)个字符

ENUM('value1','value2',...)

可以总共有65535个不同的值

SET('value1','value2',...)

最多有64个成员

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10617542/viewspace-947625/，如需转载，请注明出处，否则将追究法律责任。

MySQL中的数据类型大体分为三大类，数值类型，时间日期类型以及字符串类型。下面将对这三种类型进行详细的介绍。

一、数值类型

MySQL 支持所有标准SQL 中的数值类型，其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL 和NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和DOUBLE PRECISION)，并在标准SQL的基础上扩展增加了 TINYINT、MEDIUMINT 和BIGINT 这3 种长度不同的整型和存放位数据的BIT类型。在日常建表定义当中，整型中最常用的依旧是int类型的数据，浮点型中则视情况而定。每种数据类型表示的范围如下表所示：

mediumint无符号最大为：16777215

注：我曾经在电信行业和银行业参与过IT项目的开发，在使用他们提供的数据的时候总会发现数据库中有小数点后数字时大都用的Decimal这种数据类型，而不是采用开发中常用的而且最大取值范围一样的Double类型。Double和Decimal都属于浮点型，但是Decimal在精度上面来说，它的精度是Double的两倍，Float的四倍，在银行或者通信这些对数据要求十分精确的行业，Decimal是优于Double的更好的选择。但是需要注意的事，Decimal也是浮点类型的数据，只不过相对来说精度更高，在实际使用中如果小数点后位数过多一样会存在精度损失的问题。在《阿里巴巴Java编程规范》，明确提出使用Decimal取代double和float

为整型指定宽度，如INT(11)，对于存储来说INT(1)和INT(20)是相同的，它不会限制值的合法范围，只是规定了MySQL与客户端的交互应该显示多少位而已，比如你向INT(1)中插入了123456值，数据库中其实已经存入了123456，只是对于客户端查出来是1而已。

二、时间日期类型

MySQL中提供了五种类型的时间类型，分别为DATE，DATETIME，TIMESTAMP，TIME和YEAR。下表是日期类型的详细区间。

如果要用来表示年月日，通常用DATE 来表示。

如果要用来表示年月日时分秒，通常用DATETIME 表示。

如果只用来表示时分秒，通常用TIME 来表示。

如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP 来表示。

TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19 个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。如果只是表示年份，可以用YEAR 来表示，它比DATE 占用更少的空间。YEAR 有2 位或4 位格式的年。默认是4 位格式。在4 位格式中，允许的值是1901～2155 和0000。在2 位格式中，允许的值是70～69，表示从1970～2069 年。MySQL 以YYYY 格式显示YEAR值。

三、字符串类型

MySQL 中提供了多种对字符数据的存储类型，不同的版本可能有所差异。以5.0 版本为例，MySQL 包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET 等多种数据类型。其中常用的为VARCHAR，TEXT等。其中CHAR和VARCHAR存在以下区别

1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (10)，表示你存储的字符将占10个字节(包括7个空字符)，而同样的

VARCHAR2 (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2．CHAR的效率比VARCHAR2的效率稍高。

3．目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，

这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

下表列出了MySQL中所有的字符类型。

在实际的建表定义字段类型时，字符串通常会考虑使用VARCHAR，当字段数据有明确的精度和长度时使用CHAR更加高效。在有长文本输入时选择TEXT输入。其它的在平常开发中很少用到了。