mysql longbolb_MySql基本数据类型及约束

1. 常用的数据类型(data_type)

字符串类型

CHAR(n) : 固定长度

VARCHAR(n) : 可变长度

NCHAR(n) : 使用utf8存储，固定长度

NVARCHAR(n) : 使用utf8存储，可变长度

BLOB : (2^16 - 1)字节

LONGBOLB : (2^32 - 1)字节

ENUM(‘value1‘,‘value2‘,...) : 枚举，填入的值必须是枚举列表中的某一个

数字类型

INT, INTEGER (n) : 标准整型

DOUBLE(m,n) : 标准双精度浮点型， m为总长度，n为小数精确位数(0<= n <=6)

BOOL, BOOLEAN : 值为零则视为false，非零视为true

日期和时间类型

DATE : 以‘YYYY-MM-DD‘形式现实

DATETIME(fsp) : 以‘YYYY-MM-DD HH:MM:SS[.fsp]‘格式显示, (0 <= fsp <= 6)

TIME(fsp) : 以‘HH:MM:SS[.fsp]‘格式显示

YEAR : 以‘YYYY‘格式显示

JSONS数据类型(从MySql5.7.8开始支持)

JSON

JSON数据类型的优势:

自动验证存储在JSON列中的JSON文档 。无效的文档会产生错误。

优化的存储格式。存储在JSON列中的JSON文档将 转换为内部格式，以允许快速读取文档元素。当服务器稍后必须读取以此二进制格式存储的JSON值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需在文档中读取它们之前或之后的所有值。

2. MySql表级约束和列级约束

表级约束

PRIMARY KEY

UNIQUE

CHECK(exp)

FOREIGN KEY (column-name, ......) REFERENCES foreign-table (column-name, ......) ON [DELETE | UPDATE] [SET NULL | RESTRICT | CASCADE | NO ACTION | SET DEFAULT]

列级约束

PRIMARY KEY, AUTO_INCREMENT

NOT NULL

UNIQUE

CHECK(exp)

DEFAULT

REFERENCES foreign-table (column-name, ......) ON [DELETE | UPDATE] [SET NULL | RESTRICT | CASCADE | NO ACTION | SET DEFAULT]

注意： 整数或浮点列可以具有附加属性AUTO_INCREMENT。通常该值为 value+1 ，这里 value 是目前在表中的列的最大值。如果插入NULL时自动为该列设至此值。

3. Example

CREATE TABLE T_DEPT

(

DEPTNO INTEGER(2) PRIMARY KEY,

DNAME VARCHAR(15),

LOC VARCHAR(15)

) DEFAULT CHARACTER SET utf8;

INSERT INTO T_DEPT VALUES(10, ‘ACCOUNTING‘, ‘NEW YORK‘);

INSERT INTO T_DEPT VALUES(20, ‘RESEARCH‘, ‘DALLAS‘);

INSERT INTO T_DEPT VALUES(30, ‘SALES‘, ‘CHICAGO‘);

INSERT INTO T_DEPT VALUES(40, ‘OPERATIONS‘, ‘BOSTON‘);

CREATE TABLE T_EMP

(

EMPNO INTEGER(4) PRIMARY KEY,

ENAME VARCHAR(15),

JOB VARCHAR(9),

MGR INTEGER(4),

HIREDATE DATE,

SAL DOUBLE(7,2),

COMM DOUBLE(7,2),

DEPTNO INTEGER(2) REFERENCES T_DEPT(DEPTNO) ON UPDATE CASCADE

) DEFAULT CHARACTER SET utf8;

INSERT INTO T_EMP VALUES(7369, ‘SMITH‘, ‘CLERK‘, 7902, CURRENT_DATE, 800, NULL, 20);

INSERT INTO T_EMP VALUES(7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, CURRENT_DATE, 1600, 300, 30);

INSERT INTO T_EMP VALUES(7521, ‘WARD‘, ‘SALESMAN‘, 7698, CURRENT_DATE, 1250, 500, 30);

INSERT INTO T_EMP VALUES(7566, ‘JONES‘, ‘MANAGER‘, 7839, CURRENT_DATE, 2975, NULL, 20);

INSERT INTO T_EMP VALUES(7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, CURRENT_DATE, 1250, 1400, 30);

INSERT INTO T_EMP VALUES(7698, ‘BLAKE‘, ‘MANAGER‘, 7839, CURRENT_DATE, 2850, NULL, 30);

INSERT INTO T_EMP VALUES(7782, ‘CLARK‘, ‘MANAGER‘, 7839, CURRENT_DATE, 2450, NULL, 10);

INSERT INTO T_EMP VALUES(7788, ‘SCOTT‘, ‘ANALYST‘, 7566, CURRENT_DATE, 3000, NULL, 20);

INSERT INTO T_EMP VALUES(7839, ‘KING‘, ‘PRESIDENT‘,NULL, CURRENT_DATE, 5000, NULL, 10);

INSERT INTO T_EMP VALUES(7844, ‘TURNER‘, ‘SALESMAN‘, 7698, CURRENT_DATE, 1500, 0, 30);

INSERT INTO T_EMP VALUES(7876, ‘ADAMS‘, ‘CLERK‘, 7788, CURRENT_DATE, 1100, NULL, 20);

INSERT INTO T_EMP VALUES(7900, ‘JAMES‘, ‘CLERK‘, 7698, CURRENT_DATE, 950, NULL, 30);

INSERT INTO T_EMP VALUES(7902, ‘FORD‘, ‘ANALYST‘, 7566, CURRENT_DATE, 3000, NULL, 20);

INSERT INTO T_EMP VALUES(7934, ‘MILLER‘, ‘CLERK‘, 7782, CURRENT_DATE, 1300, NULL, 10);

CREATE TABLE T_SALGRADE

(

GRADE INTEGER PRIMARY KEY AUTO_INCREMENT,

LOSAL INTEGER,

HISAL INTEGER

) DEFAULT CHARACTER SET utf8;

INSERT INTO T_SALGRADE VALUES(1, 700, 1200);

INSERT INTO T_SALGRADE VALUES(2, 1201, 1400);

INSERT INTO T_SALGRADE VALUES(3, 1401, 2000);

INSERT INTO T_SALGRADE VALUES(4, 2001, 3000);

INSERT INTO T_SALGRADE VALUES(5, 3001, 9999);

CREATE TABLE T_JSON

(

JOSN_TEXT JSON DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO T_JSON VALUES (

‘{"type": [{"lavel": 1, "price": 500.12}, {"lavel": 2, "price": 800.78}], "width": 50, "height": 100}‘);

--搜索JSON值

mysql> SELECT JSON_EXTRACT(JOSN_TEXT, ‘$.width‘) from T_JSON;

+------------------------------------+

| JSON_EXTRACT(JOSN_TEXT, ‘$.width‘) |

+------------------------------------+

| 50 |

+------------------------------------+

mysql> SELECT JSON_EXTRACT(JOSN_TEXT, ‘$.type[1].lavel‘) from T_JSON;

+--------------------------------------------+

| JSON_EXTRACT(JOSN_TEXT, ‘$.type[1].lavel‘) |

+--------------------------------------------+

| 2 |

+--------------------------------------------+