Mysql数据类型
INT整形类型---属性
unsigned/signed
- 无符号/有符号
- unsigned无符号，创建一个无符号的列
mysql> create table z ( a int unsigned ); Query OK, 0 rows affected (0.75 sec)
- signed有符号
mysql> create table z (a int unsigned, b tinyint signed ); Quey OK, 0 rows affected (0.08 sec)
- 无符号的列不能插入-号，有符号的列可以插入-号
插入数据
mysql> insert into z values ( 1,-1); Query OK, 1 row affected (0.00 sec)
mysql> insert into z values ( -2,3); ERROR 1264 (22003): Out of range value for column 'a' at row 1
zerofill
- 显示属性
- 值不做任何修改
mysql> show create table z\G
*************************** 1. row ***************************
Table: z
Create Table: CREATE TABLE `z` (
`a` int(10) unsigned DEFAULT NULL,
`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
填充显示长度对属性没有影响
mysql> alter table z change column a a int(4) unsigned zerofill; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
新插入数据
mysql> insert into z values (100000,10); Query OK, 1 row affected (0.01 sec) mysql> select * from z; +--------+------+ | a | b | +--------+------+ | 0001 | -1 | | NULL | -1 | | NULL | -1 | | 0002 | 3 | | 100000 | 10 | +--------+------+ 5 rows in set (0.00 sec)
auto_increment
- 自增
- 每张表一个
- 必须是索引的一部分
- 如果使用int作为主键自增，需要使用BIGINT类型
mysql> alter table z change column a a int auto_increment
primary key;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table z\G
*************************** 1. row ***************************
Table: z
Create Table: CREATE TABLE `z` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
插入值测试
mysql> insert into z values (NULL,10); Query OK, 1 row affected (0.00 sec) mysql> insert into z values (NULL,20); Query OK, 1 row affected (0.00 sec) mysql> insert into z values (NULL,30); Query OK, 1 row affected (0.00 sec)
mysql> select * from z; +---+------+ | a | b | +---+------+ | 1 | 10 | | 2 | 20 | | 3 | 30 | +---+------+ 3 rows in set (0.00 sec)
自增值的回溯
- 比如自增值4被删除了，那么永远也不会有4这个值被插入，重启后插入数据会恢复[5.7.33版本正常]
int类型总结
- 推荐不要使用UNSIGNED
- 范围本质上没有大的变化
- UNSIGNED可能会有益出现象发生
- 自增INT类型主键建议使用BIGINT
小数类型
float 4个字节
- 单精度类型
double 8个字节
- 双精度类型
如果用上面两个类型统计数据会出错，M*G/G不一定等于M
decimal
- 高精度类型
日期时间类型
datetime 年月日时分秒
//now转换为整形 mysql> select now()+0; +----------------+ | now()+0 | +----------------+ | 20210621105843 | +----------------+ 1 row in set (0.10 sec)
date年月日
字符串类型---字符集
常见字符集：
- utf8，utf8mb4，gbk，gb18030
char(N),varchar(N)是有字符集 要求的，
binary（N），varbinary（N），是没有字符集的存一切
mysql> show variables like '%character%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /data/mysql/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)修改表的字符集
>alter table z convert to charater set utf8mb4;varchar(N)变长字符串
字符串类型---排序规则
密码加密方法
>insert into user values (md5(concat('12345678','xxx')));
产生随机字符串长度
>select repeat('a',floor(1+rand()*127));mysql> select repeat('a',floor(1+rand()*127));
+---------------------------------+
| repeat('a',floor(1+rand()*127)) |
+---------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaa |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select repeat('a',floor(1+rand()*127));
+------------------------------------------------------------+
| repeat('a',floor(1+rand()*127)) |
+------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+------------------------------------------------------------+
1 row in set (0.00 sec)
BLOB 不带字符集
TEXT 带字符集
JSON类型
5.7版本支持
- 原生JSON类型替换BLOB类型
- JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查
- 查询性能的提升：查询不需要便利所有字符串才能找到数据
- 支持部分属性索引：通过虚拟列的功能可以对json中的部分数据进行索引
schema free
- 无需预定义列的数量
- 无需修改表结构
创建一个表其中一列属性为json
mysql> create table user (uid int auto_increment,
-> data json,primary key(uid))engine=innodb;
插入数据
mysql> insert into user (uid,data) values (NULL,'{"name":"David","address":"Shanghai" }');
Query OK, 1 row affected (0.17 sec)插入不同信息的数据
mysql> insert into user (uid,data) values (NULL,'{"name":"jim","passport":"E0231883" }');
Query OK, 1 row affected (0.05 sec)
mysql> select * from user;
+-----+------------------------------------------+
| uid | data |
+-----+------------------------------------------+
| 1 | {"name": "David", "address": "Shanghai"} |
| 2 | {"name": "jim", "passport": "E0231883"} |
+-----+------------------------------------------+
2 rows in set (0.00 sec)
json类型取出name字段的数据
- 方法一：json_extrac
mysql> select uid,json_extract(data,"$.name") from user;
+-----+-----------------------------+
| uid | json_extract(data,"$.name") |
+-----+-----------------------------+
| 1 | "David" |
| 2 | "jim" |
+-----+-----------------------------+
2 rows in set (0.38 sec)json_unquote去除冒号
mysql> select uid,json_unquote(json_extract(data,"$.name")) from user; +-----+-------------------------------------------+ | uid | json_unquote(json_extract(data,"$.name")) | +-----+-------------------------------------------+ | 1 | David | | 2 | jim | +-----+-------------------------------------------+ 2 rows in set (0.00 sec)
- 方法二：
不去除引号
mysql> select uid,data->"$.name" from user; +-----+----------------+ | uid | data->"$.name" | +-----+----------------+ | 1 | "David" | | 2 | "jim" | +-----+----------------+ 2 rows in set (0.00 sec)
去除引号
mysql> select uid,data->>"$.name" from user; +-----+-----------------+ | uid | data->>"$.name" | +-----+-----------------+ | 1 | David | | 2 | jim | +-----+-----------------+ 2 rows in set (0.00 sec)
继续阅读
我的微信
这是我的微信扫一扫
评论