P64 二进制类型与JSON类型详解、数据类型小结与建议

10.二进制字符串类型

#10.1 binary与varbinary
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
#f3 VARBINARY,
f4 VARBINARY(10)
);

DESC test_binary1;

INSERT INTO test_binary1(f1,f2)
VALUES('a','abc');

SELECT * FROM test_binary1;

#Data too long for column 'f1' at row 1
INSERT INTO test_binary1(f1)
VALUES('ab');

INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');

SELECT LENGTH(f2),LENGTH(f4)
FROM test_binary1; # f2是3(因为是固定长度)，f4是2

#10.2 Blob类型

CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);

INSERT INTO test_blob1(id)
VALUES(1001);

SELECT * FROM test_blob1;

11.JSON类型

CREATE TABLE test_json(
js json
);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');

select * from test_json;

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'
AS province, js -> '$.address.city' AS city
from test_json;

第12章 小结与建议