📂sql / SQL基础
🔖sql
2022-06-17 13:00阅读: 128评论: 0推荐: 0

数据类型

mysql数据类型

1. 数值类型

a. 整型

类型         字节     范围（有符号位）
tinyint     1字节    -128 ~ 127      无符号位：0 ~ 255
smallint    2字节    -32768 ~ 32767
mediumint   3字节    -8388608 ~ 8388607
int         4字节
bigint      8字节

int 是 MySQL 中的一种整数类型，它可以存储带符号或无符号的整数。

  1. 在 MySQL 中，int 和 integer 是完全相同的类型。它们都是用于存储整数的类型。integer 是 int 的同义词，它也被 MySQL 识别为整数类型。

  2. 在 MySQL 中，int 类型可以是 32 位或 64 位，具体取决于底层操作系统和 MySQL 版本。

从 MySQL 8.0 开始，默认情况下 int 类型是 64 位。在 MySQL 5.7 及更早版本中，默认情况下 int 类型是 32 位。

可以通过以下方式查看 MySQL 中 int 类型的位数：

1) 使用 SHOW VARIABLES 语句：

SHOW VARIABLES LIKE 'innodb_default_row_format';

输出结果中 innodb_default_row_format 的值为 Compact 或 Dynamic，表示 int 类型是 32 位；值为 Barracuda，表示 int 类型是 64 位。

2) 使用 INFORMATION_SCHEMA 数据库中的 COLUMNS 表：

SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'stuTab'
AND COLUMN_NAME = 'age';

输出结果中 COLUMN_TYPE 的值为 int(11)，表示 int 类型是 32 位。输出结果中 COLUMN_TYPE 的值为 int(20)，表示 int 类型是 64 位。

  1. 默认存在符号位，unsigned 属性修改。
stuID INT UNSIGNED,

UNSIGNED 关键字可以用于将整型列定义为无符号类型，从而扩大存储范围、提高效率并防止意外负数。

  1. 在 MySQL 5.7 及更高版本中，还引入了 int(M) 语法，其中 M 表示字段的显示宽度。
stuID int(11)  该字段最多可以显示 11 位数字

显示宽度决定了在 MySQL 客户工具中显示数字时使用的宽度。但是，不会影响字段的存储空间。int(11) 和 int 类型的字段都占用 4 个字节的存储空间。

因此，在大多数情况下，使用 int 或 integer 类型即可满足需求。只有在需要控制字段显示宽度的情况下，才需要使用 int(M) 语法。

32位的int，不指定M，默认是int(11)。

mysql5 innodb下，说 int(1) 只占一个字节，但可以正常存储9999，所以这种说法感觉不太准确。

b. 浮点型

类型             字节     范围
float(单精度)     4字节
double(双精度)    8字节
浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
    不同于整型，前后均会补填0.
定义浮点型时，需指定总位数和小数位数。
    float(M, D)     double(M, D)
    M表示总位数，D表示小数位数。
    M和D的大小会决定浮点数的范围。不同于整型的固定范围。
    M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
    支持科学计数法表示。
    浮点数表示近似值。

c. 定点数

decimal -- 可变长度
decimal(M, D)   M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。

2. 二进制类型

MySQL中的BIT, BINARY, VARBINARY, BLOB（包括TINYBLOB, BLOB, MEDIUMBLOB, 和 LONGBLOB） 都属于二进制字符串类型，它们主要用于存储二进制数据，如图像、音频、视频、压缩文件、加密数据等非文本内容。这些类型之间的关系和区别主要体现在以下几个方面：

a. BIT

bit 类型：以位为单位存储二进制数据。通常用于表示二进制数据或标志位。不能用于存储浮点数或字符串。

bit(n) 语法：指定存储n个位长度的二进制，其中 n 可以是 1 到 64 之间的任何整数。

存储的时候使用，n位能够表示的整数十进制赋值，如果使用true/false赋值，只能相当于1/0.

如果不指定长度，默认为1位。赋值的时候支持两种方式（true/false）（1/0） 1等价于true，0等价于false。bit可以用于表示 boolean类型

create table tab(
    id int,
    sex bit -- 默认 bit(1)
);

insert into tab(id,sex) values(1,1);

insert into tab(id,sex) values(2,true);

mysql> select * from tab;
+----+-----+
| id | sex |
+----+-----+
|  1 | 1   |
|  2 | 1   |
|  3 | 1   |
|  4 | 0   |
+----+-----+
4 rows in set (0.04 sec)

mysql>

b. BINARY 和 VARBINARY

BINARY 和 VARBINARY 类型：以字节为单位存储二进制数据。

  • BINARY 用于存储固定长度的二进制数据，需要在声明时指定最大长度（M），并始终占用指定的字节数（BINARY 值会在右侧填充 0x00（零字节）以达到指定的长度）。

BINARY(M)如果不指定M，默认为1.

  • VARBINARY 用于存储可变长度的二进制数据，同样需要指定最大长度（M），但实际存储的数据仅占用实际数据的字节数加上额外的字节用于存储长度信息。

特点: 两者均适合存储较小的二进制数据，如加密密钥、哈希值、UUID等。BINARY 固定长度可能导致空间浪费，但访问速度可能更快；VARBINARY 则更节省空间，尤其适合存储长度可变的数据。

CREATE TABLE tab5 (
  binary_col BINARY -- 默认binary(1)
);

INSERT INTO tab5 (binary_col) VALUES (0x12);

INSERT INTO tab5 (binary_col) VALUES (0b1100);


CREATE TABLE tab6 (
  binary_col BINARY(2)
);

INSERT INTO tab6 (binary_col) VALUES (0x12);

INSERT INTO tab6 (binary_col) VALUES (0b1100);

select * from tab6;
0x1200
0x0C00

c. BLOB 家族

BLOB 类型用于存储大量的二进制数据。这种类型适用于存储较大的二进制对象，如图片、音频文件、大型文档等。它们都是变长类型，可以根据实际存储的数据自动调整所占空间。

有四种 BLOB 类型：

  • TINYBLOB 最多可存储 2^8 - 1 字节（255字节）。
  • BLOB 最多可存储 2^16 - 1 字节（65,535字节）。
  • MEDIUMBLOB 最多可存储 2^24 - 1 字节（16,777,215字节）。
  • LONGBLOB 最多可存储 2^32 - 1 字节（约4GB）。

image-20240409230127898

总结

总的来说，VARBINARY 在功能上与 BLOB 几乎相同（从存储内容的角度来看），除非您需要与“旧”版本的 MySQL 兼容。MySQL 文档中指出：在大多数情况下，您可以将 BLOB 列视为可以无限大的 VARBINARY 列。

需要注意的是，VARBINARY 存储在行内，因此如果不知道如何操作，可能会严重影响查询性能。对于大型二进制数据，建议使用 BLOB，因为其外部存储不会影响查询性能。

如果您只需存储二进制数据，而不需要索引或查询，那么从 PHP 的角度来看，这两种类型之间没有明显的优势。

3. 字符串类型

MySQL中的CHAR, VARCHAR, TEXT（包括TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT）都是用于存储文本数据的类型。

a. char, varchar

以字符为单位存储字符。

  1. CHAR:
  • 用途: 用于存储定长的字符串，即固定字符数量的文本数据。
  • 长度: 在声明时需指定长度（M），且每个CHAR列始终占用指定长度的字节空间。即使实际存储的文本不满M个字符，也会用空格（或特定填充字符）补足到指定长度。
  • 特点: 适合存储长度固定且变化范围小的文本，如邮政编码、电话号码、固定格式的代码等。由于长度固定，查询效率相对较高，但也可能导致空间浪费。
  1. VARCHAR:
  • 用途: 用于存储变长的字符串，即长度可变的文本数据。
  • 长度: 在声明时需指定最大长度（M），但实际存储的数据仅占用实际字符数加额外的字节（通常为1或2字节，用于存储长度信息）。
  • 特点: 适合存储长度可变且可能较长的文本，如姓名、地址、产品描述等。相比于CHAR，VARCHAR更节省空间，尤其是对于大多数值未达到最大长度的情况。
char,最多255个字符，与编码无关。
varchar,最多65535字符，与编码有关。
    一条有效记录最大不能超过65535个字节。
    
varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。

例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3

不同的编码，所占用的空间不同。
utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符

在 MySQL 5.0 之前，VARCHAR 类型是按字节计算长度的，一个中文字符占两个字节。在 MySQL 5.0 及更高版本中，VARCHAR 类型是按字符计算长度的，一个中文字符占一个字符。
VARCHAR(255) 表示该列最多可以存储 255 个字符，包括空格。

插入中文时报错：

[HY000][1366] Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'name' at row 1

MySQL 的默认字符编码为 latin1，该编码不支持中文。

要解决这个问题，需要将 数据库的字符编码 和 表的字符集 都设置为支持中文的编码，例如 UTF-8。

  • 创建表的时候直接指定字符编码
-- 创建数据库时指定字符编码
CREATE DATABASE test CHARACTER SET utf8mb4;

-- 创建表时指定字符编码
1. 直接在定义name列时通过使用关键词CHARACTER SET指定了name列将使用字符集为utf8mb4。
CREATE TABLE tab7 (
    name VARCHAR(255) CHARACTER SET utf8mb4
);

2. 在表的创建语句末尾添加了表选项 `default charset = utf8mb4` 设置了表的默认字符集为utf8mb4。
CREATE TABLE tab7 (
    name VARCHAR(255)
) engine = InnoDB default charset = utf8mb4;

这意味着如果没有在列级别明确指定字符集，则所有列都将默认使用utf8mb4字符集。由于本例中只有name这一列且未在其定义中单独指定字符集，因此name列实际上也是使用utf8mb4字符集。

3. 可以同时配置，列中指定的字符编码会覆盖表级的字符编码
CREATE TABLE tab8 (
    name VARCHAR(255) CHARACTER SET latin1
) engine = InnoDB default charset = utf8mb4;



-- 插入中文数据
INSERT INTO tab7 (name) VALUES ('中文');
  • 创建表之后，修改字符编码
-- 修改数据库的字符编码:
ALTER DATABASE test CHARACTER SET utf8mb4;

-- 修改表的字符集:
ALTER TABLE tab7 CHARACTER SET utf8mb4;

-- 查看数据库的字符编码
SHOW VARIABLES LIKE 'character_set_database';

-- 查看表的字符集（查看建表语句）
SHOW CREATE TABLE tab7;
  • 建表语句后面的default CHARACTER SET utf8mb4 和 default charset = utf8mb4 的区别

在MySQL中，default CHARACTER SET utf8mb4 和 default charset = utf8mb4 作用是相同的，它们都是用来设置数据库、表或列的默认字符集为utf8mb4。

在正式文档和推荐实践中，通常倾向于使用完整且明确的default CHARACTER SET语法，以保持代码的一致性和清晰度。

列后面好像只能跟default CHARACTER SET utf8mb4。

修改数据库或表的字符编码可能会导致数据库或表中的现有数据乱码。因此，在修改数据库的字符编码之前，需要对数据库进行备份。最好在创建表的时候直接指定。

b. TEXT 家族

TEXT 类型用于存储大容量的文本数据。它有四个子类型：

  • TINYTEXT 最多可存储 2^8 - 1 字节（255字节）。
  • TEXT 最多可存储 2^16 - 1 字节（65,535字节）。
  • MEDIUMTEXT 最多可存储 2^24 - 1 字节（16,777,215字节）。
  • LONGTEXT 最多可存储 2^32 - 1 字节（约4GB）。
text 在定义时，不需要定义长度，也不会计算总长度。
text 类型在定义时，不可给default值

总结

总的来说，VARCHAR 和 TEXT 都是可变长度的字符串类型，它们只会占用实际存储的字符数。CHAR 则是固定长度的字符串类型，始终占用指定的存储空间。如果您需要存储大量文本数据，TEXT 类型是更好的选择。

需要注意的是，VARCHAR 和 TEXT 的存储空间取决于字符数，而不是最大长度。因此，选择合适的类型可以有效地管理存储空间和性能。

特点: 这些类型适用于存储大量文本数据，如文章内容、用户评论、日志记录等。它们都是变长类型，可以根据实际存储的数据自动调整所占空间。

一般原则是：

  • 对于长度固定且较小的文本，如标识符或短代码，优先考虑使用CHAR。
  • 对于长度可变且变化范围较大的文本，优先考虑使用VARCHAR。
  • 对于需要存储大段文本（如文章、报告、日志等）的情况，选择合适的TEXT类型，根据实际最大可能的文本长度来决定使用哪个级别的TEXT类型。

4. 日期时间类型

year

year 类型用于存储年份，格式为 YYYY，例如 "2023"。使用1个字节来存储，存储范围 1901 - 2155。

Date

DATE 类型用于存储日期，格式为 YYYY-MM-DD，例如 "2023-11-16"。

DATE 类型使用 3 个字节来存储日期数据。

其中，第一个字节存储年份的后两位，第二个字节存储月份，第三个字节存储日期。

create table tab1(
    date1 date
);

insert into tab1(date1) value ('1997-12-7');

select * from tab1;

可以使用 DATE_FORMAT() 函数来指定日期格式

INSERT INTO tab1 (date1) VALUES (DATE_FORMAT('2023--11--18', '%Y--%m--%d'));

SELECT DATE_FORMAT(date1, '%Y-%m-%d %H:%i:%s') FROM tab1; -- 没有时间信息，使用全0填充

time

time 类型用于存储时间，格式为 hh:mm:ss，例如 "11:11:11"。

time 类型使用 3 个字节来存储时间数据。

create table tab2(
    time1 time
);

insert into tab2(time1) value ('11:11:11');

SELECT DATE_FORMAT(time1, '%Y-%m-%d %H:%i:%s') FROM tab2; -- 没有日期信息，使用当天日期填充

插入毫秒信息
insert into tab2(time1) value ('11:11:11.111');
SELECT DATE_FORMAT(time1, '%Y-%m-%d %H:%i:%s.%f') FROM tab2;
  • DATE_FORMAT函数只影响输出结果，不会改变数据库中存储的日期和时间数据。
  • 要使用DATE_FORMAT函数格式化出毫秒，需要确保数据库中存储的日期和时间数据包含毫秒信息。

mysql:5.7.41 中不保存毫秒信息。

datetime

datetime类型用于存储日期及时间，格式为yyyy-MM-dd HH:mm:ss，例如："1997-12-3 11:11:11"。

存储范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59，占 8 个字节的存储空间。

create table tab3(
    datetime1 datetime
);

insert into tab3(datetime1) value ('1997-12-3 11:11:11');

select * from tab3;

timestamp

timestamp类型用于存储日期和时间，格式为yyyy-MM-dd HH:mm:ss，例如："1997-12-3 11:11:11"。

存储范围是 1970-01-01 00:00:00 到 2038-01-19 03:14:07，占 4 个字节的存储空间。

如果将来不给这个字段赋值（赋值为null），则会使用当前系统时间来自动赋值。

create table tab4(
    timestamp1 timestamp
);

insert into tab4(timestamp1) value (null);

select * from tab4;

5. 枚举和集合

枚举(enum)

 enum(val1, val2, val3...)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
表现为字符串类型，存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。

集合（set）

set(val1, val2, val3...)
create table tab ( gender set('男', '女', '无') );
insert into tab values ('男, 女');
    最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
    当创建表时，SET成员值的尾部空格将自动被删除。

oracle数据类型

  • 字符型

    • char

      • 定长，最大2000字符
      • char(10) 小韩”前四个字符放“小韩”后添6个空格补全

    • varchar2(20)

      • 变长，最大4000字符
      • varchar2(10) 小寒oracle分配四个字符这样可以节省空间

    • clob（character large object）

      • 字符型大对象，最大4G

  • 数字型

    • number

      • 范围 -10^38 - 10^38
      • 可以表示整数，也可以表示小数

    • number(5)

      • 表示一个五位整数
      • 范围-99999 99999

    • number(5,2)

      • 表示一个小数有5位有效数，2位小数
      • 范围 -999.99 999.99

  • 日期

    • date

      • 包含年月日和时分秒

    • timestamp

      • 这是oracle9i对date数据类型的扩展

    • oracle默认日期格式'DD-MON-YY'

      • dd日 mon月 yy 2位的年

    • 改日期默认格式

      • alter session set nls_date_format = 'yyyy-mm-dd';

  • 图片

    • blob
      • 二进制数据 可以存放图片、声音
        • 4G

Oracle LOB

  • LOB，即Large Objects（大对象），是用来存储大量的二进制和文本数据的一种数据类型（一个LOB字段可存储可多达4GB的数据）。
  • LOB 分为两种类型：内部LOB和外部LOB。
    • 内部LOB将数据以字节流的形式存储在数据库的内部。因而，内部LOB的许多操作都可以参与事务，也可以像处理普通数据一样对其进行备份和恢复操作。
    • Oracle支持三种类型的内部LOB：
      • BLOB（二进制数据）
      • CLOB（单字节字符数据）
      • NCLOB（多字节字符数据）。
    • CLOB和NCLOB类型适用于存储超长的文本数据，BLOB字段适用于存储大量的二进制数据，如图像、视频、音频，文件等。

目前只支持一种外部LOB类型，即BFILE类型。在数据库内，该类型仅存储数据在操作系统中的位置信息，而数据的实体以外部文件的形式存在于操作系统的文件系统中。因而，该类型所表示的数据是只读的，不参与事务。该类型可帮助用户管理大量的由外部程序访问的文件。

使用JDBC来写入Blob型数据到Oracle中

  • Oracle的Blob字段比long字段的性能要好，可以用来保存如图片之类的二进制数据。
  • Oracle的BLOB字段由两部分组成：数据（值）和指向数据的指针（定位器）。 尽管值与表自身一起存储，但是一个BLOB列并不包含值，仅有它的定位指针。为了使用大对象，程序必须声明定位器类型的本地变量。
  • 当Oracle内部LOB被创建时，定位器被存放在列中，值被存放在LOB段中，LOB段是在数据库内部表的一部分。
  • 因为Blob自身有一个cursor，当写入Blob字段必须使用指针（定位器）对Blob进行操作，因而在写入Blob之前，必须获得指针（定位器）才能进行写入
  • 如何获得Blob的指针（定位器） ：需要先插入一个empty的blob，这将创建一个blob的指针，然后再把这个empty的blob的指针查询出来，这样通过两步操作，就获得了blob的指针，可以真正的写入blob数据了。

1、插入空blob
insert into javatest(name,content) values(?,empty_blob());
2、获得blob的cursor
select content from javatest where name= ? for update;
注意: 须加for update，锁定该行，直至该行被修改完毕，保证不产生并发冲突。
3、利用 io，和获取到的cursor往数据库写数据流

本文作者：jiyuchen1

本文链接：https://www.cnblogs.com/jiyuchen1/p/16385390.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   姬雨晨  阅读(128)  评论(0)  编辑  收藏  举报
点击右上角即可分享
微信分享提示
AI IDE Trae
💬
评论
📌
收藏
💗
关注
👍
推荐
🚀
回顶
➖
收起