处理字符串和 Unicode

Microsoft SQL 提供了多种字符串类型，mssql-python 驱动将这些字符串映射到 Python str 对象。 关键决定是使用 varchar （非Unicode）还是 nvarchar （Unicode）：

• 当你的数据可能包含ASCII以外的字符时，请使用 nvarchar ，比如姓名、地址或任何语言的用户生成内容。
• 当数据严格是ASCII（代码、标识符、电子邮件地址）且你想节省存储时使用 varchar 。 varchar 每个字符使用1字节; nvarchar 每个字符使用2字节。

基本字符串操作

驱动程序将所有 Microsoft SQL 字符串类型映射到 Python str 对象。

插入和取回字符串

使用参数化查询安全地插入和获取数据库中的字符串数据。

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create temp table for demo
cursor.execute("""
    CREATE TABLE #StringDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100),
        Email NVARCHAR(200)
    )
""")

# Insert string data
cursor.execute(
    "INSERT INTO #StringDemo (Name, Email) VALUES (%(name)s, %(email)s)",
    {"name": "Alice Smith", "email": "alice@example.com"}
)
conn.commit()

# Retrieve string data
cursor.execute("SELECT Name, Email FROM #StringDemo WHERE ID = 1")
row = cursor.fetchone()
print(row.Name)   # 'Alice Smith'
print(row.Email)  # 'alice@example.com'

带有特殊字符的字符串

使用参数化查询处理引号、角括号及字符串中的其他特殊字符。

# Quotes and special characters handled automatically
cursor.execute("""
    CREATE TABLE #Notes (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Title NVARCHAR(200),
        Content NVARCHAR(MAX)
    )
""")
cursor.execute(
    "INSERT INTO #Notes (Title, Content) VALUES (%(title)s, %(content)s)",
    {
        "title": "O'Brien's Report",
        "content": 'Contains "quotes" and special chars: <>&'
    }
)
conn.commit()

Unicode 支持

使用nvarchar列和Pythonstr来存储和检索任何语言的文本。

存储Unicode文本

通过将 Python 字符串传递给参数化查询插入 Unicode 内容;驱动对 nvarchar 列进行 UTF-16LE 编码。

# International characters - use nvarchar columns
cursor.execute("""
    CREATE TABLE #Messages (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Content NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Messages (Content) VALUES (%(msg)s)
""", {"msg": "Hello 你好 مرحبا שלום 🎉"})

cursor.execute("SELECT Content FROM #Messages WHERE ID = 1")
row = cursor.fetchone()
print(row.Content)  # 'Hello 你好 مرحبا שלום 🎉'

不同文字中的Unicode

通过使用 nvarchar 列和批量插入，支持单一表中的多种语言和脚本。

messages = [
    {"lang": "English", "text": "Hello, World!"},
    {"lang": "Chinese", "text": "你好，世界！"},
    {"lang": "Japanese", "text": "こんにちは世界！"},
    {"lang": "Korean", "text": "안녕하세요, 세상!"},
    {"lang": "Arabic", "text": "مرحبا بالعالم!"},
    {"lang": "Hebrew", "text": "שלום עולם!"},
    {"lang": "Russian", "text": "Привет мир!"},
    {"lang": "Greek", "text": "Γειά σου Κόσμε!"},
    {"lang": "Emoji", "text": "👋🌍✨🎉"},
]

cursor.execute("""
    CREATE TABLE #Greetings (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Language NVARCHAR(50),
        Message NVARCHAR(200)
    )
""")
cursor.executemany("""
    INSERT INTO #Greetings (Language, Message) VALUES (%(lang)s, %(text)s)
""", messages)
conn.commit()

确保Unicode的nvarchar列

当数据可能包含非ASCII字符时，务必将列定义为nvarchar而非varchar。

-- For Unicode data, always use nvarchar, not varchar
CREATE TABLE #UnicodeDemo (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100),        -- Supports Unicode
    Description NVARCHAR(MAX)  -- Supports large Unicode text
);

字符串长度注意事项

根据数据长度的一致性，选择固定长度和可变长度类型。

固定长度与可变长度

在 Microsoft SQL 中，char(n) 会用尾随空格填充至声明的长度。 这种填充会浪费可变长度数据的存储空间，但可以提升固定宽度列（如国家代码）的性能。 大多数字符串列都用 varchar(n) 。

以下示例展示了填充列与非填充列处理数据检索的差异：

# char(6) pads to fixed length
cursor.execute(
    "SELECT StateProvinceCode FROM Person.StateProvince WHERE StateProvinceID = 1"
)  # nchar(6) column
row = cursor.fetchone()
print(repr(row.StateProvinceCode))  # 'AB    ' - right-padded with spaces

# nvarchar stores actual length
cursor.execute(
    "SELECT Name FROM Person.StateProvince WHERE StateProvinceID = 1"
)  # nvarchar column
row = cursor.fetchone()
print(repr(row.Name))  # 'Alberta' - no padding

处理行尾空格

在从固定长度字符列检索数据时，请使用 rstrip() 以移除 Microsoft SQL Server 添加的填充空间。

# Strip trailing spaces from char columns
cursor.execute("SELECT ProductNumber FROM Production.Product")
for row in cursor:
    code = row.ProductNumber.rstrip()  # Remove trailing spaces
    print(f"Code: '{code}'")

大型字符串（MAX 类型）

nvarchar(max) 和 varchar(max) 类型支持最大可达 2 GB 的字符串，非常适合存储大型文本文档、JSON 或 XML 内容。

# Large text content
large_content = "x" * 100000  # 100K characters

cursor.execute("""
    CREATE TABLE #Documents (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Content NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Documents (Content) VALUES (%(content)s)
""", {"content": large_content})

cursor.execute("SELECT Content FROM #Documents WHERE ID = 1")
row = cursor.fetchone()
print(len(row.Content))  # 100000

字符串比较与排序

Microsoft SQL 字符串比较的行为取决于数据库或列上所设置的排序规则。

事例敏感性

Microsoft SQL 字符串比较依赖于排序。 默认情况下，大多数数据库使用不区分大小写的排序规则，但你可以使用 COLLATE 子句覆盖这一默认设置。

# Case-insensitive collation (default for many databases)
cursor.execute("SELECT * FROM Person.Person WHERE LastName = %(name)s", {"name": "smith"})
# Might match 'Smith', 'SMITH', 'smith' depending on collation

# For case-sensitive comparison
cursor.execute("""
    SELECT * FROM Person.Person 
    WHERE LastName COLLATE Latin1_General_CS_AS = %(name)s
""", {"name": "Smith"})

LIKE 模式匹配

使用 LIKE 带有万用字符的操作符搜索字符串模式;用括号符号转义特殊字符以匹配文字。

# Wildcard searches
search_term = "Road"
cursor.execute("""
    SELECT Name FROM Production.Product WHERE Name LIKE %(pattern)s
""", {"pattern": f"%{search_term}%"})

# Escape special characters in search
def escape_like(value: str) -> str:
    """Escape LIKE wildcards in search value."""
    return value.replace("[", "[[]").replace("%", "[%]").replace("_", "[_]")

search = "100%"
cursor.execute("""
    SELECT Name FROM Production.Product WHERE Name LIKE %(pattern)s
""", {"pattern": f"%{escape_like(search)}%"})

编码注意事项

编码行为取决于 Microsoft SQL 的列类型和源代码的排序。

编码假设与Unicode默认值

mssql-python驱动程序根据 Microsoft SQL 列类型自动处理编码。 默认情况下，字符串参数对于 nvarchar 列以 UTF-16LE 编码发送，对于 varchar 列则按照数据库的排序规则发送：

Python字符串内部始终是Unicode的。 当你传递参数 str 时，驱动会为目标列类型编码该参数。 默认情况下，驱动以字符串参数 nvarchar （Unicode）形式发送，确保字符无论数据库如何整合都能被保留。 对于 varchar 列，只有当数据库或列使用支持 UTF-8 的排序规则时，UTF-8 才适用。

如果你的列是， varchar 并且你需要发送非Unicode数据以完全匹配列类型（例如，避免隐式转换警告），请使用 setinputsizes() 覆盖默认值：

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create temp table for demo
cursor.execute("CREATE TABLE #AsciiTable (Code VARCHAR(100))")

cursor.setinputsizes([(mssql_python.SQL_VARCHAR, 100, 0)])
cursor.execute(
    "INSERT INTO #AsciiTable (Code) VALUES (?)",
    ("ABC123",)
)
conn.commit()

对于大多数应用，默认行为是正确的。 只有在查询计划中看到隐含的转换警告或需要匹配特定 varchar 汇合时才会覆盖。

连接编码

mssql-python驱动自动处理基于Microsoft SQL Server版本和配置的连接编码。 由于 Python 字符串是 Unicode，驱动程序会根据目标数据类型对其进行适当编码（UTF-8 或 UTF-16）。 你不需要手动配置连接编码。

带有遗留排序的VARCHAR列

采用 Windows-1252（CP1252）排序规则的数据库（如 Latin1_General_CI_AS）会在使用 CP1252 编码的 varchar 列中存储扩展拉丁字符（例如 €、™ 以及带重音符号的字符）。 驱动程序在所有平台上都能正确解码这些字符。

这一差异对跨平台部署尤为重要：varcharWindows上正确读取的数据在Linux上也能正确读取，无需特殊配置。

# Create a temp table with a varchar column and insert extended Latin characters
cursor.execute("CREATE TABLE #Products (Name VARCHAR(100))")
cursor.execute("INSERT INTO #Products (Name) VALUES (%(name)s)", {"name": "Café €100 ™"})
conn.commit()

# CP1252 characters in varchar columns are decoded correctly on all platforms
cursor.execute("SELECT Name FROM #Products WHERE Name LIKE '%€%'")
for row in cursor:
    print(row.Name)  # Correct on both Windows and Linux

如果你的架构允许，将 varchar 列迁移到 nvarchar 可以彻底避免编码歧义，并支持所有 Unicode 字符。

文件编码

读取要插入数据库的文件时，请指定合适的编码以保留Unicode内容。

# Reading files with explicit encoding
def insert_file_content(cursor, conn, file_path: str, encoding: str = "utf-8"):
    with open(file_path, "r", encoding=encoding) as f:
        content = f.read()
    
    cursor.execute(
        "INSERT INTO #FileContent (Content) VALUES (%(content)s)",
        {"content": content}
    )
    conn.commit()

常见字符串操作

这些示例涵盖了Python和SQL中常见的字符串操作模式。

串联

你可以在插入前用 Python 串接字符串，或者在服务器上用 SQL 的字符串运算符。

# Concatenate in Python before insert
first_name = "Alice"
last_name = "Smith"
full_name = f"{first_name} {last_name}"

cursor.execute("""
    CREATE TABLE #ConcatDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        FullName NVARCHAR(200)
    )
""")
cursor.execute(
    "INSERT INTO #ConcatDemo (FullName) VALUES (%(name)s)",
    {"name": full_name}
)

# Or concatenate in SQL
cursor.execute("""
    SELECT FirstName + ' ' + LastName AS FullName FROM Person.Person
""")

字符串格式设置

在向用户显示之前，在 Python 中应用格式设置，以便将字符串显示为带有货币格式、填充或对齐的形式。

from decimal import Decimal

# Format for display
cursor.execute("SELECT Name, ListPrice FROM Production.Product WHERE ListPrice > 0")
for row in cursor.fetchall()[:5]:
    print(f"{row.Name}: ${row.ListPrice:.2f}")

# Pad strings
cursor.execute("SELECT ProductNumber FROM Production.Product")
for row in cursor.fetchall()[:5]:
    padded = row.ProductNumber.ljust(15)  # Left-justify, pad to 15 chars
    print(f"[{padded}]")

NULL 与空字符串

Microsoft SQL 将 NULL 和空字符串（''）视为不同的值。 NULL 表示“未知”，空字符串表示“已知为空”。为你的申请选择一个惯例并保持一致。 大多数应用程序使用 NULL 来表示缺少的可选字段。

以下示例演示了如何区分NULL字符串和空字符串：

# NULL is different from empty string
cursor.execute("""
    CREATE TABLE #NullDemo (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100),
        MiddleName NVARCHAR(100)
    )
""")
cursor.execute("""
    INSERT INTO #NullDemo (Name, MiddleName) 
    VALUES (%(name)s, %(middle)s)
""", {"name": "Alice", "middle": None})  # NULL

cursor.execute("""
    INSERT INTO #NullDemo (Name, MiddleName) 
    VALUES (%(name)s, %(middle)s)
""", {"name": "Bob", "middle": ""})  # Empty string

# Query differences
cursor.execute("SELECT * FROM #NullDemo WHERE MiddleName IS NULL")
cursor.execute("SELECT * FROM #NullDemo WHERE MiddleName = ''")

修剪操作

使用Python的字符串方法，去除数据库中取值时的前置、后置或两者兼有的空白。

cursor.execute("SELECT Name FROM Production.Product")
for row in cursor:
    # Remove whitespace
    trimmed = row.Name.strip()  # Both ends
    left_trimmed = row.Name.lstrip()
    right_trimmed = row.Name.rstrip()

JSON 字符串数据

将 JSON 文档存储在 nvarchar（max） 列中，并用 Microsoft SQL 的 JSON 函数查询它们。

Store JSON as nvarchar

将 Python 字典序列化为 JSON 字符串并插入 nvarchar 列中；再将其取出并反序列化为 Python 对象。

import json

data = {"name": "Alice", "scores": [95, 87, 91], "active": True}
json_string = json.dumps(data)

cursor.execute("""
    CREATE TABLE #Configs (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        ConfigData NVARCHAR(MAX)
    )
""")
cursor.execute("""
    INSERT INTO #Configs (ConfigData) VALUES (%(data)s)
""", {"data": json_string})

# Retrieve and parse
cursor.execute("SELECT ConfigData FROM #Configs WHERE ID = 1")
row = cursor.fetchone()
config = json.loads(row.ConfigData)
print(config["name"])  # 'Alice'

使用 Microsoft SQL JSON 函数

使用Microsoft SQL的JSON函数，直接在查询中解析和过滤JSON数据，而不是客户端代码。

import json

data = {"name": "Alice", "scores": [95, 87, 91], "active": True}

cursor.execute("""
    CREATE TABLE #Configs (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        ConfigData NVARCHAR(MAX)
    )
""")
cursor.execute(
    "INSERT INTO #Configs (ConfigData) VALUES (%(data)s)",
    {"data": json.dumps(data)}
)
conn.commit()

cursor.execute("""
    SELECT JSON_VALUE(ConfigData, '$.name') AS Name
    FROM #Configs
    WHERE JSON_VALUE(ConfigData, '$.active') = 'true'
""")
for row in cursor:
    print(row.Name)  # 'Alice'

全文搜索

使用 LIKE 进行模式匹配，或启用全文索引以进行更高级的文本搜索。

全文查询

当无法获得全文索引时，带有通配符图案的 LIKE 操作符为全文搜索提供了一种直接的替代方案。

# Using CONTAINS (requires full-text index on the table)
cursor.execute("""
    SELECT JobTitle FROM HumanResources.Employee
    WHERE JobTitle LIKE %(search)s
""", {"search": "%Engineer%"})

# Pattern-based search as an alternative to full-text
cursor.execute("""
    SELECT Name FROM Production.Product
    WHERE Name LIKE %(search)s
""", {"search": "%Mountain%"})

最佳做法

应用这些指南，正确处理跨语言和编码的字符串数据。

使用nvarchar获取国际数据

如果你不确定某列是否包含 Unicode，可以使用 nvarchar。 存储成本适中，且防止字符转换导致的数据丢失。

以下示例说明了为 Unicode 数据和仅 ASCII 数据定义列之间的区别：

-- Good: supports any language
CREATE TABLE #UserProfile (
    Name NVARCHAR(100),
    Bio NVARCHAR(MAX)
);

-- Limited: ASCII/Latin only
CREATE TABLE #UserProfileAscii (
    Name VARCHAR(100),
    Bio VARCHAR(MAX)
);

验证字符串长度

插入前请在 Python 中检查字符串长度，以防止截断错误并为用户提供有意义的错误信息。

def safe_insert(cursor, name: str, max_length: int = 100):
    """Insert with length validation."""
    if len(name) > max_length:
        raise ValueError(f"Name exceeds {max_length} characters")
    
    cursor.execute(
        "INSERT INTO #UserProfile (Name) VALUES (%(name)s)",
        {"name": name}
    )

单独处理二进制字符串

区分文本字符串（Pythonstr、SQLnvarchar）和二进制数据（Pythonbytes、SQL varbinary），以避免编码问题。

binary_data = b'\x00\x01\x02'  # bytes - use varbinary
text_data = "Hello"            # str - use nvarchar

相关内容

• 数据类型映射
• 处理 NULL 值
• 二进制数据