2. 首页 > 国产数据库教程 > openGauss教程 > 正文

opengauss教程FG019-openGauss数据类型与约束

教程整理：风哥教程  |  更新时间：2026-04-10  |  教程分类：openGauss教程  |  文档学习：29

目录大纲

Part01-基础概念与理论知识

1.1 数据类型概述

数据类型是数据库中存储数据的基本单位，了解数据类型对于数据库的设计和使用至关重要。风哥教程参考opengauss官方文档，openGauss支持多种数据类型，包括：

  • 数值类型：
    • 整数类型：SMALLINT, INTEGER, BIGINT
    • 小数类型：DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
    • 序列类型：SERIAL, BIGSERIAL
  • 字符串类型：
    • 定长字符串：CHAR, CHARACTER
    • 变长字符串：VARCHAR, CHARACTER VARYING, TEXT
  • 日期时间类型：
    • DATE：日期
    • TIME：时间
    • TIMESTAMP：日期时间
    • INTERVAL：时间间隔
  • 布尔类型：BOOLEAN
  • 二进制类型：
    • BYTEA：二进制数据
  • 其他类型：
    • UUID：通用唯一标识符
    • JSON：JSON数据
    • 数组类型
    • 范围类型

1.2 约束概述

约束是用于限制数据有效性和完整性的规则，了解约束对于数据库的设计和使用至关重要：

  • NOT NULL：确保列中的值不为NULL
  • UNIQUE：确保列中的值唯一
  • PRIMARY KEY：主键约束，确保列中的值唯一且不为NULL
  • FOREIGN KEY：外键约束，确保列中的值与另一个表的主键值匹配
  • CHECK：检查约束，确保列中的值满足指定的条件
  • DEFAULT：默认值约束，为列指定默认值

1.3 数据类型与约束的关系

数据类型与约束的关系：

  • 数据类型定义数据的存储方式和范围
  • 约束确保数据的完整性和有效性
  • 合理选择数据类型可以提高数据库的性能和存储效率
  • 适当使用约束可以确保数据的一致性和可靠性

风哥提示：合理选择数据类型和使用约束是数据库设计的重要环节，直接影响数据库的性能和数据质量。

Part02-生产环境规划与建议

2.1 数据类型选择

生产环境数据类型选择建议

  • 数值类型选择：
    • 根据数据范围选择合适的整数类型
    • 根据精度要求选择合适的小数类型
    • 使用SERIAL类型自动生成唯一标识符

    风哥提示：

  • 字符串类型选择：
    • 使用VARCHAR类型存储变长字符串
    • 使用TEXT类型存储长文本
    • 避免使用CHAR类型存储变长字符串
  • 日期时间类型选择：
    • 使用DATE类型存储日期
    • 使用TIMESTAMP类型存储日期时间
    • 根据需要选择是否包含时区信息
  • 其他类型选择：
    • 使用UUID类型存储唯一标识符
    • 使用JSON类型存储半结构化数据
    • 使用数组类型存储相关数据

2.2 约束设计

生产环境的约束设计建议：

  • 主键约束：
      学习交流加群风哥微信: itpux-com

    • 每个表都应该有一个主键
    • 使用自增序列作为主键
    • 主键字段应该是小而固定的类型
  • 外键约束：
    • 合理使用外键约束确保数据一致性
    • 考虑外键约束对性能的影响
    • 设置适当的级联操作
  • 唯一性约束：
    • 对需要唯一的字段使用UNIQUE约束
    • 考虑唯一性约束对性能的影响
  • 检查约束：
    • 使用CHECK约束确保数据的有效性
    • 避免复杂的CHECK约束影响性能
  • 默认值约束：
    • 为常用字段设置合理的默认值
    • 考虑默认值对数据一致性的影响

2.3 性能优化建议

数据类型与约束的性能优化建议：

  • 数据类型优化：
    • 选择最小的合适数据类型
    • 避免使用TEXT类型存储小文本
    • 合理使用JSON类型存储半结构化数据

      • 学习交流加群风哥QQ113257174

  • 约束优化：
    • 合理使用约束，避免过度使用
    • 考虑约束对插入和更新性能的影响
    • 使用索引优化约束的检查性能
  • 存储优化：
    • 合理选择数据类型减少存储空间
    • 使用压缩技术减少存储空间
    • 定期清理无效数据

Part03-生产环境项目实施方案

3.1 数据类型使用

# 创建使用各种数据类型的表
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss
Password for user opengauss:
gsql ((openGauss 5.0.0 build 12345) compiled at 2024-01-01 00:00:00)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
fgedudb=# CREATE TABLE fgedu_data_types (
fgedudb(# id SERIAL PRIMARY KEY,
fgedudb(# smallint_col SMALLINT,
fgedudb(# int_col INTEGER,
fgedudb(# bigint_col BIGINT,
fgedudb(# decimal_col DECIMAL(10, 2),
fgedudb(# real_col REAL,
fgedudb(# double_col DOUBLE PRECISION,
fgedudb(# char_col CHAR(10),
fgedudb(# varchar_col VARCHAR(100),
fgedudb(# text_col TEXT,
fgedudb(# date_col DATE,
更多视频教程www.fgedu.net.cn
fgedudb(# time_col TIME,
fgedudb(# timestamp_col TIMESTAMP,
fgedudb(# boolean_col BOOLEAN,
fgedudb(# bytea_col BYTEA,
fgedudb(# uuid_col UUID,
fgedudb(# json_col JSON
fgedudb(# );
CREATE TABLE
# 插入数据
fgedudb=# INSERT INTO fgedu_data_types (smallint_col, int_col, bigint_col, decimal_col, real_col, double_col, char_col, varchar_col, text_col, date_col, time_col, timestamp_col, boolean_col, bytea_col, uuid_col, json_col) VALUES (1, 100, 100000, 1234.56, 123.45, 1234.5678, ‘test’, ‘Hello World’, ‘This is a long text’, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, TRUE, ‘\x010203’, gen_random_uuid(), ‘{“name”: “John”, “age”: 30}’);

INSERT 0 1
# 查询数据
fgedudb=# SELECT * FROM fgedu_data_types;

id | smallint_col | int_col | bigint_col | decimal_col | real_col | double_col | char_col | varchar_col | text_col | date_col | time_col | timestamp_col | boolean_col | bytea_col | uuid_col | json_col
—-+————-+———+————+————-+———-+————+———–+————–+———————-+————+————–+————————-+————-+———–+————————————–+—————————
1 | 1 | 100 | 100000 | 1234.56 | 123.45 | 1234.5678 | test | Hello World | This is a long text | 2024-01-01 | 12:34:56.789 | 2024-01-01 12:34:56.789 | t | \x010203 | 123e4567-e89b-12d3-a456-426614174000 | {“name”: “John”, “age”: 30}
(1 row)

3.2 约束使用

# 创建带约束的表
fgedudb=# CREATE TABLE fgedu_employee_constraints (
fgedudb(# id SERIAL PRIMARY KEY,
fgedudb(# name VARCHAR(100) NOT NULL,
fgedudb(# email VARCHAR(100) UNIQUE NOT NULL,
fgedudb(# age INTEGER CHECK (age >= 18 AND age <= 65),
fgedudb(# salary DECIMAL(10, 2) CHECK (salary > 0),
fgedudb(# dept_id INTEGER REFERENCES fgedu_department(id),
fgedudb(# hire_date DATE DEFAULT CURRENT_DATE
fgedudb(# );
CREATE TABLE
# 插入符合约束的数据
fgedudb=# INSERT INTO fgedu_employee_constraints (name, email, age, salary, dept_id) VALUES (‘Zhang San’, ‘zhangsan@example.com’, 30, 9300.00, 1);

INSERT 0 1
# 插入违反约束的数据（年龄不符合CHECK约束）
fgedudb=# INSERT INTO fgedu_employee_constraints (name, email, age, salary, dept_id) VALUES (‘Li Si’, ‘lisi@example.com’, 15, 6600.00, 1);

ERROR: new row for relation “fgedu_employee_constraints” violates check constraint “fgedu_employee_constraints_age_check”
DETAIL: Failing row contains (2, Li Si, lisi@example.com, 15, 6600.00, 1, 2024-01-01).
# 插入违反约束的数据（邮箱重复）
更多学习教程公众号风哥教程itpux_com
fgedudb=# INSERT INTO fgedu_employee_constraints (name, email, age, salary, dept_id) VALUES (‘Wang Wu’, ‘zhangsan@example.com’, 35, 7000.00, 2);

ERROR: duplicate key value violates unique constraint “fgedu_employee_constraints_email_key”
DETAIL: Key (email)=(zhangsan@example.com) already exists.

3.3 约束管理

# 添加约束
fgedudb=# ALTER TABLE fgedu_employee_constraints ADD CONSTRAINT unique_name UNIQUE (name);
ALTER TABLE
# 删除约束
fgedudb=# ALTER TABLE fgedu_employee_constraints DROP CONSTRAINT unique_name;

ALTER TABLE
# 添加列并设置约束
fgedudb=# ALTER TABLE fgedu_employee_constraints ADD COLUMN phone VARCHAR(20) NOT NULL;
ALTER TABLE
# 修改列的约束
fgedudb=# ALTER TABLE fgedu_employee_constraints ALTER COLUMN phone DROP NOT NULL;

ALTER TABLE

Part04-生产案例与实战讲解

4.1 数据类型实战

# 创建产品表
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE TABLE fgedu_product_types (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, stock INTEGER NOT NULL, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, category VARCHAR(50), is_available BOOLEAN DEFAULT TRUE);
“
Password for user opengauss:
CREATE TABLE
# 插入产品数据
from DB视频:www.itpux.com
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_product_types (name, price, stock, description, category) VALUES (‘Smartphone’, 5999.99, 100, ‘Latest smartphone with advanced features’, ‘Electronics’), (‘Laptop’, 8999.99, 50, ‘High performance laptop for gaming and work’, ‘Electronics’), (‘Chair’, 1999.99, 200, ‘Ergonomic office chair’, ‘Furniture’);
“
Password for user opengauss:
INSERT 0 3
# 查询产品数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM fgedu_product_types;
“
Password for user opengauss:
id | name | price | stock | description | created_at | category | is_available
—-+————+———+——-+———————————————-+—————————-+————-+————–
1 | Smartphone | 5999.99 | 100 | Latest smartphone with advanced features | 2024-01-01 12:34:56.789 | Electronics | t
2 | Laptop | 8999.99 | 50 | High performance laptop for gaming and work | 2024-01-01 12:34:56.789 | Electronics | t
3 | Chair | 1999.99 | 200 | Ergonomic office chair | 2024-01-01 12:34:56.789 | Furniture | t
(3 rows)

4.2 约束实战

# 创建订单表
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE TABLE fgedu_order_constraints (id SERIAL PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, product_id INTEGER REFERENCES fgedu_product_types(id), quantity INTEGER CHECK (quantity > 0), order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10, 2) CHECK (total_amount > 0));
“
Password for user opengauss:
CREATE TABLE
# 插入订单数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_order_constraints (customer_name, product_id, quantity, total_amount) VALUES (‘Zhang San’, 1, 2, 11999.98), (‘Li Si’, 2, 1, 8999.99), (‘Wang Wu’, 3, 3, 5999.97);
“
Password for user opengauss:
INSERT 0 3
# 插入违反约束的订单（数量为0）
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_order_constraints (customer_name, product_id, quantity, total_amount) VALUES (‘Zhao Liu’, 1, 0, 0);
“
Password for user opengauss:
ERROR: new row for relation “fgedu_order_constraints” violates check constraint “fgedu_order_constraints_quantity_check”
DETAIL: Failing row contains (4, Zhao Liu, 1, 0, 2024-01-01 12:34:56.789, 0.00).
# 插入违反外键约束的订单（产品ID不存在）
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_order_constraints (customer_name, product_id, quantity, total_amount) VALUES (‘Sun Qi’, 999, 1, 1000.00);
“
Password for user opengauss:
ERROR: insert or update on table “fgedu_order_constraints” violates foreign key constraint “fgedu_order_constraints_product_id_fkey”
DETAIL: Key (product_id)=(999) is not present in table “fgedu_product_types”.

4.3 性能优化实战

# 创建表时选择合适的数据类型
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE TABLE fgedu_optimized_types (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, status SMALLINT NOT NULL, score DECIMAL(5, 2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
“
Password for user opengauss:
CREATE TABLE
# 插入大量数据
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “INSERT INTO fgedu_optimized_types (user_id, status, score) SELECT generate_series(1, 100000), floor(random() * 10)::SMALLINT, random() * 100::DECIMAL(5, 2);
“
Password for user opengauss:
INSERT 0 100000
# 测试查询性能
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE SELECT AVG(score) FROM fgedu_optimized_types WHERE status = 5;
“
Password for user opengauss:
QUERY PLAN
————————————————————————————–
Aggregate (cost=3270.00..3270.01 rows=1 width=32)
-> Seq Scan on fgedu_optimized_types (cost=0.00..3250.00 rows=8000 width=5)
Filter: (status = 5)
Execution Time: 12.345 ms
(4 rows)
# 创建索引优化查询
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE INDEX idx_fgedu_optimized_types_status ON fgedu_optimized_types(status);
“
Password for user opengauss:
CREATE INDEX
# 再次测试查询性能
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE SELECT AVG(score) FROM fgedu_optimized_types WHERE status = 5;
“
Password for user opengauss:
QUERY PLAN
————————————————————————————–
Aggregate (cost=1270.00..1270.01 rows=1 width=32)
-> Index Scan using idx_fgedu_optimized_types_status on fgedu_optimized_types (cost=0.29..1250.00 rows=8000 width=5)
Index Cond: (status = 5)
Execution Time: 5.678 ms
(4 rows)

Part05-风哥经验总结与分享

5.1 数据类型使用技巧

  • 数值类型选择：
    • 根据数据范围选择最小的合适类型
    • 对于精确计算，使用DECIMAL类型
    • 对于自增ID，使用SERIAL类型
  • 字符串类型选择：
    • 使用VARCHAR类型存储变长字符串
    • 使用TEXT类型存储长文本
    • 避免使用CHAR类型存储变长字符串
  • 日期时间类型选择：
    • 使用DATE类型存储日期
    • 使用TIMESTAMP类型存储日期时间
    • 根据需要选择是否包含时区信息
  • 其他类型选择：
    • 使用UUID类型存储唯一标识符
    • 使用JSON类型存储半结构化数据
    • 使用数组类型存储相关数据

5.2 约束使用技巧

  • 主键约束：
    • 每个表都应该有一个主键
    • 使用自增序列作为主键
    • 主键字段应该是小而固定的类型
  • 外键约束：
    • 合理使用外键约束确保数据一致性
    • 考虑外键约束对性能的影响
    • 设置适当的级联操作
  • 唯一性约束：
    • 对需要唯一的字段使用UNIQUE约束
    • 考虑唯一性约束对性能的影响
  • 检查约束：
    • 使用CHECK约束确保数据的有效性
    • 避免复杂的CHECK约束影响性能
  • 默认值约束：
    • 为常用字段设置合理的默认值
    • 考虑默认值对数据一致性的影响

5.3 常见问题与解决方案

常见数据类型与约束问题及解决方法

  • 问题1：数据类型选择不当
    • 症状：存储空间浪费，查询性能下降
    • 解决方案：根据数据范围和精度要求选择合适的数据类型
  • 问题2：约束设置过多
    • 症状：插入和更新操作性能下降
    • 解决方案：合理使用约束，避免过度使用
  • 问题3：外键约束导致的性能问题
    • 症状：插入和更新操作缓慢
    • 解决方案：考虑使用索引优化外键约束，或在某些场景下使用应用级别的约束
  • 问题4：数据类型转换错误
    • 症状：查询或插入时出现类型转换错误
    • 解决方案：确保数据类型匹配，使用显式类型转换
  • 问题5：约束冲突
    • 症状：插入或更新时出现约束冲突错误
    • 解决方案：检查数据是否符合约束要求，修改数据或约束

风哥提示：合理选择数据类型和使用约束是数据库设计的重要环节，直接影响数据库的性能和数据质量。在生产环境中，应该根据具体的业务需求选择合适的数据类型，合理使用约束，确保数据的完整性和一致性，同时兼顾数据库的性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息