sql-text-image-functions

SQL - 处理字符串和 LOB 数据

本章涵盖了在 SQL 中处理文本和大对象（LOB）数据的现代技术。我们将探讨标准字符串操作函数，并讨论存储文档或图像等大型数据的最佳实践。

注意：函数 TEXTPTR() 和 TEXTVALID() 是 SQL Server 旧版本中已弃用的功能，不应在现代开发中使用。本章将这些过时内容替换为当前符合标准的实践。

文本和二进制数据的现代数据类型

选择正确的数据类型对于性能和存储效率至关重要。

• VARCHAR(n)：用于可变长度字符串的标准，最大长度为 n。将其用于大多数文本字段，例如姓名、标题和简短描述。
• CHAR(n)：用于固定长度字符串。仅当您确定所有值的长度都完全相同时才使用此类型，例如两位数的国家/地区代码（‘US’、‘CA’、‘DE’）。否则，VARCHAR 更高效。
• TEXT / CLOB / VARCHAR(MAX)：用于可变长度、可能非常长的长格式字符数据，例如博客文章或文章。确切的名称因 SQL 方言而异（MySQL/PostgreSQL 中为 TEXT，SQL Server 中为 VARCHAR(MAX)，Oracle 中为 CLOB）。
• VARBINARY(n) / BYTEA / BLOB：用于存储可变长度的二进制数据（大对象或 LOB）。这包括图像、PDF 或其他文件。名称因方言而异（MySQL/Oracle 中为 BLOB，PostgreSQL 中为 BYTEA，SQL Server 中为 VARBINARY(MAX)）。

常见的 SQL 字符串函数

大多数 SQL 数据库支持一组标准的函数来操作字符串数据。让我们使用 products 表进行演示。

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  sku VARCHAR(20)
);

INSERT INTO products (id, name, sku) VALUES
(1, '  Modern SQL Guide  ', 'SQL-MOD-001'),
(2, 'learning python', 'PY-LRN-002');

函数	示例	描述与结果
CONCAT()	SELECT CONCAT(name, ' (', sku, ')') FROM products WHERE id = 1;	连接字符串。结果: Modern SQL Guide (SQL-MOD-001)
LENGTH() / LEN()	SELECT LENGTH(sku) FROM products WHERE id = 1;	返回字符数。结果: 11
UPPER() / LOWER()	SELECT UPPER(name) FROM products WHERE id = 2;	转换大小写。结果: LEARNING PYTHON
TRIM()	SELECT TRIM(name) FROM products WHERE id = 1;	移除开头和结尾的空格。结果: Modern SQL Guide
SUBSTRING()	SELECT SUBSTRING(sku FROM 1 FOR 3) FROM products WHERE id = 1;	提取子字符串。结果: SQL
REPLACE()	SELECT REPLACE(name, 'python', 'Python 3') FROM products WHERE id = 2;	替换子字符串的出现。结果: learning Python 3
POSITION()	SELECT POSITION('LRN' IN sku) FROM products WHERE id = 2;	查找子字符串的起始位置。结果: 4

存储大对象（图像、文件）的最佳实践

虽然您可以使用 BLOB 或 VARBINARY(MAX) 列将图像等二进制数据直接存储在数据库中，但这通常不是现代应用程序推荐的方法。将文件存储在数据库中可能导致：

• **数据库大小增加：**迅速膨胀数据库，导致备份和恢复缓慢且昂贵。
• **性能问题：**数据库服务器针对结构化数据检索进行了优化，而不是用于服务大型二进制文件。
• **应用程序复杂性：**需要额外的应用程序逻辑来处理数据库之间的数据流。

现代方法：对象存储

行业标准最佳实践是将文件存储在专用的对象存储服务中，并在数据库中仅存储对文件的引用（URL 或唯一键）。

工作流程：

1. 应用程序将用户的文件（例如，个人资料图片）直接上传到 Amazon S3、Google Cloud Storage 或 Azure Blob Storage 等对象存储服务。
2. 存储服务返回存储文件的唯一 URL 或标识符。
3. 应用程序将此 URL/标识符与用户其他数据一起保存在数据库的 VARCHAR 列中。

示例模式

-- 存储图像的引用，而不是图像本身
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    bio TEXT,
    profile_picture_url VARCHAR(255)  -- 存储S3等服务上的图像URL
);

这种方法使您的数据库保持精简和快速，同时利用专门服务发挥其最佳优势。