Spanner数据类型 #

Spanner数据类型
├── 标量类型 (Scalar Types)
│   ├── 数值类型
│   ├── 布尔类型
│   ├── 字符串类型
│   ├── 字节类型
│   ├── 日期时间类型
│   └── JSON类型
│
├── 数组类型 (ARRAY)
│   └── 标量类型的数组
│
└── 结构体类型 (STRUCT)
    └── 命名字段集合

-- 64位有符号整数
-- 范围: -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807

CREATE TABLE numbers (
    id INT64 NOT NULL,
    value INT64
) PRIMARY KEY (id);

-- 插入数据
INSERT INTO numbers (id, value) VALUES (1, 1234567890);
INSERT INTO numbers (id, value) VALUES (2, -9876543210);

-- 整数字面量
SELECT 123;                    -- 十进制
SELECT 0x1F;                   -- 十六进制
SELECT 0o17;                   -- 八进制

-- 64位IEEE 754浮点数
-- 范围: 约 ±1.7976931348623157e308

CREATE TABLE measurements (
    id INT64 NOT NULL,
    temperature FLOAT64,
    pressure FLOAT64
) PRIMARY KEY (id);

-- 插入数据
INSERT INTO measurements (id, temperature, pressure)
VALUES (1, 23.5, 1013.25);

-- 浮点字面量
SELECT 3.14;
SELECT 2.5e10;                 -- 科学计数法
SELECT CAST('inf' AS FLOAT64); -- 无穷大
SELECT CAST('nan' AS FLOAT64); -- 非数字

-- 数学函数
SELECT ABS(-123);              -- 绝对值: 123
SELECT ROUND(3.14159, 2);      -- 四舍五入: 3.14
SELECT FLOOR(3.7);             -- 向下取整: 3
SELECT CEIL(3.2);              -- 向上取整: 4
SELECT SQRT(16);               -- 平方根: 4
SELECT POWER(2, 10);           -- 幂运算: 1024
SELECT LOG(100);               -- 自然对数
SELECT LOG10(100);             -- 以10为底的对数: 2

-- 类型转换
SELECT CAST('123' AS INT64);   -- 字符串转整数
SELECT CAST(123 AS FLOAT64);   -- 整数转浮点
SELECT SAFE_CAST('abc' AS INT64); -- 安全转换，失败返回NULL

-- 布尔类型，值为 TRUE 或 FALSE

CREATE TABLE flags (
    id INT64 NOT NULL,
    is_active BOOL NOT NULL,
    is_deleted BOOL DEFAULT FALSE
) PRIMARY KEY (id);

-- 插入数据
INSERT INTO flags (id, is_active) VALUES (1, TRUE);
INSERT INTO flags (id, is_active) VALUES (2, FALSE);

-- 布尔字面量
SELECT TRUE;
SELECT FALSE;
SELECT NULL::BOOL;

-- 逻辑运算
SELECT TRUE AND FALSE;         -- FALSE
SELECT TRUE OR FALSE;          -- TRUE
SELECT NOT TRUE;               -- FALSE

-- 比较运算
SELECT 1 = 1;                  -- TRUE
SELECT 1 != 2;                 -- TRUE
SELECT 1 < 2;                  -- TRUE
SELECT 1 > 0;                  -- TRUE
SELECT 1 <= 1;                 -- TRUE
SELECT 1 >= 1;                 -- TRUE

-- NULL处理
SELECT TRUE AND NULL;          -- NULL
SELECT FALSE OR NULL;          -- NULL
SELECT NOT NULL;               -- NULL

-- 安全比较
SELECT 1 IS NULL;              -- FALSE
SELECT NULL IS NULL;           -- TRUE
SELECT NULL IS NOT NULL;       -- FALSE

-- 变长字符串，必须指定最大长度
-- STRING(n): 最大n个字符
-- STRING(MAX): 最大2,621,442个字符

CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255),
    bio STRING(MAX)
) PRIMARY KEY (user_id);

-- 插入数据
INSERT INTO users (user_id, name, email, bio)
VALUES (1, 'John Doe', 'john@example.com', 'A software developer');

-- 字符串字面量
SELECT 'Hello, World!';        -- 普通字符串
SELECT "Hello, World!";        -- 双引号也支持
SELECT r'Raw\nString';         -- 原始字符串，不转义
SELECT '''Multi
line
string''';                      -- 多行字符串

-- 字符串操作
SELECT LENGTH('Hello');        -- 长度: 5
SELECT UPPER('hello');         -- 大写: HELLO
SELECT LOWER('HELLO');         -- 小写: hello
SELECT TRIM('  hello  ');      -- 去空格: hello
SELECT LTRIM('  hello');       -- 去左空格
SELECT RTRIM('hello  ');       -- 去右空格

-- 子字符串
SELECT SUBSTR('Hello', 1, 3);  -- 子串: Hel
SELECT LEFT('Hello', 3);       -- 左边3个: Hel
SELECT RIGHT('Hello', 3);      -- 右边3个: llo

-- 查找和替换
SELECT STRPOS('Hello', 'l');   -- 查找位置: 3
SELECT REPLACE('Hello', 'l', 'L'); -- 替换: HeLLo
SELECT SPLIT('a,b,c', ',');    -- 分割: ['a', 'b', 'c']

-- 拼接
SELECT CONCAT('Hello', ' ', 'World'); -- Hello World
SELECT 'Hello' || ' ' || 'World';     -- Hello World

-- 正则表达式
SELECT REGEXP_CONTAINS('Hello', '^[A-Z]'); -- TRUE
SELECT REGEXP_EXTRACT('Hello123', '[0-9]+'); -- 123
SELECT REGEXP_REPLACE('Hello123', '[0-9]+', '!'); -- Hello!

-- 变长字节数组
-- BYTES(n): 最大n字节
-- BYTES(MAX): 最大10,485,760字节

CREATE TABLE files (
    file_id INT64 NOT NULL,
    name STRING(255) NOT NULL,
    content BYTES(MAX),
    thumbnail BYTES(10240)
) PRIMARY KEY (file_id);

-- 插入数据
INSERT INTO files (file_id, name, content)
VALUES (1, 'test.bin', b'\x00\x01\x02\x03');

-- 字节字面量
SELECT b'Hello';               -- 字节字面量
SELECT b'\x48\x65\x6c\x6c\x6f'; -- 十六进制表示

-- 字节操作
SELECT LENGTH(b'Hello');       -- 长度: 5
SELECT SUBSTR(b'Hello', 1, 3); -- 子串: b'Hel'

-- 编码转换
SELECT FROM_BASE64('SGVsbG8='); -- Base64解码: b'Hello'
SELECT TO_BASE64(b'Hello');     -- Base64编码: SGVsbG8=
SELECT FROM_HEX('48656C6C6F');  -- 十六进制解码
SELECT TO_HEX(b'Hello');        -- 十六进制编码

-- 字符串与字节转换
SELECT CAST('Hello' AS BYTES);  -- 字符串转字节
SELECT CAST(b'Hello' AS STRING); -- 字节转字符串

-- 日期类型，格式: YYYY-MM-DD
-- 范围: 0001-01-01 到 9999-12-31

CREATE TABLE events (
    event_id INT64 NOT NULL,
    event_date DATE NOT NULL,
    end_date DATE
) PRIMARY KEY (event_id);

-- 插入数据
INSERT INTO events (event_id, event_date)
VALUES (1, DATE '2024-01-15');

-- 日期字面量
SELECT DATE '2024-03-27';
SELECT CAST('2024-03-27' AS DATE);

-- 时间戳类型，包含日期和时间
-- 精度: 纳秒
-- 时区: UTC

CREATE TABLE logs (
    log_id INT64 NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    updated_at TIMESTAMP
) PRIMARY KEY (log_id);

-- 插入数据
INSERT INTO logs (log_id, created_at)
VALUES (1, TIMESTAMP '2024-03-27 10:30:00 UTC');

-- 时间戳字面量
SELECT TIMESTAMP '2024-03-27 10:30:00 UTC';
SELECT TIMESTAMP '2024-03-27 10:30:00+08:00'; -- 带时区

-- 当前日期时间
SELECT CURRENT_DATE();         -- 当前日期
SELECT CURRENT_TIMESTAMP();    -- 当前时间戳

-- 提取部分
SELECT EXTRACT(YEAR FROM DATE '2024-03-27');    -- 2024
SELECT EXTRACT(MONTH FROM DATE '2024-03-27');   -- 3
SELECT EXTRACT(DAY FROM DATE '2024-03-27');     -- 27
SELECT EXTRACT(DAYOFWEEK FROM DATE '2024-03-27'); -- 4 (周三)

-- 日期运算
SELECT DATE_ADD(DATE '2024-03-27', INTERVAL 1 DAY);    -- 2024-03-28
SELECT DATE_SUB(DATE '2024-03-27', INTERVAL 1 MONTH);  -- 2024-02-27
SELECT DATE_DIFF(DATE '2024-03-27', DATE '2024-03-01', DAY); -- 26

-- 时间戳运算
SELECT TIMESTAMP_ADD(TIMESTAMP '2024-03-27 10:00:00 UTC', INTERVAL 1 HOUR);
SELECT TIMESTAMP_SUB(TIMESTAMP '2024-03-27 10:00:00 UTC', INTERVAL 30 MINUTE);
SELECT TIMESTAMP_DIFF(
    TIMESTAMP '2024-03-27 12:00:00 UTC',
    TIMESTAMP '2024-03-27 10:00:00 UTC',
    HOUR
); -- 2

-- 格式化
SELECT FORMAT_DATE('%Y-%m-%d', DATE '2024-03-27');
SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP '2024-03-27 10:30:00 UTC');

-- 解析
SELECT PARSE_DATE('%Y-%m-%d', '2024-03-27');
SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-03-27 10:30:00');

-- JSON类型，存储JSON格式数据
-- 最大大小: 10MB

CREATE TABLE documents (
    doc_id INT64 NOT NULL,
    data JSON NOT NULL,
    metadata JSON
) PRIMARY KEY (doc_id);

-- 插入数据
INSERT INTO documents (doc_id, data)
VALUES (1, JSON '{"name": "John", "age": 30}');

INSERT INTO documents (doc_id, data)
VALUES (2, JSON '{
    "name": "Jane",
    "address": {
        "city": "New York",
        "zip": "10001"
    },
    "tags": ["developer", "manager"]
}');

-- JSON字面量
SELECT JSON '{"key": "value"}';
SELECT CAST('{"key": "value"}' AS JSON);

-- 提取值
SELECT JSON_VALUE(data, '$.name') FROM documents;      -- 提取为字符串
SELECT JSON_QUERY(data, '$.name') FROM documents;      -- 提取为JSON

-- 提取嵌套值
SELECT JSON_VALUE(data, '$.address.city') FROM documents;
SELECT JSON_QUERY(data, '$.address') FROM documents;

-- 提取数组元素
SELECT JSON_VALUE(data, '$.tags[0]') FROM documents;   -- 第一个元素

-- 类型转换
SELECT CAST(JSON '123' AS INT64);      -- JSON转整数
SELECT CAST(JSON '"hello"' AS STRING); -- JSON转字符串

-- JSON构建
SELECT JSON_OBJECT('name', 'John', 'age', 30);
-- {"name":"John","age":30}

SELECT JSON_ARRAY('a', 'b', 'c');
-- ["a","b","c"]

-- JSON修改
SELECT JSON_SET(JSON '{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

SELECT JSON_REMOVE(JSON '{"a":1,"b":2}', '$.a');
-- {"b":2}

-- JSON数组操作
SELECT JSON_ARRAY_LENGTH(JSON '[1, 2, 3]'); -- 3

-- 数组类型，存储相同类型的元素
-- 元素必须是标量类型

CREATE TABLE posts (
    post_id INT64 NOT NULL,
    title STRING(200) NOT NULL,
    tags ARRAY<STRING(MAX)>,
    scores ARRAY<INT64>
) PRIMARY KEY (post_id);

-- 插入数据
INSERT INTO posts (post_id, title, tags, scores)
VALUES (1, 'First Post', ['tech', 'spanner', 'database'], [1, 2, 3, 4, 5]);

INSERT INTO posts (post_id, title, tags)
VALUES (2, 'Second Post', ARRAY<STRING(MAX)>[]); -- 空数组

-- 数组长度
SELECT ARRAY_LENGTH([1, 2, 3]); -- 3

-- 访问元素(从1开始)
SELECT tags[OFFSET(0)] FROM posts; -- 第一个元素
SELECT tags[ORDINAL(1)] FROM posts; -- 第一个元素(ORDINAL从1开始)

-- 数组操作
SELECT ARRAY_CONCAT([1, 2], [3, 4]); -- [1, 2, 3, 4]
SELECT ARRAY_TO_STRING(['a', 'b', 'c'], ','); -- 'a,b,c'
SELECT STRING_TO_ARRAY('a,b,c', ','); -- ['a', 'b', 'c']

-- 数组聚合
SELECT ARRAY_AGG(name) FROM users; -- 将多行聚合成数组

-- 数组查询
SELECT * FROM posts WHERE 'tech' IN UNNEST(tags);

-- UNNEST展开
SELECT post_id, tag
FROM posts, UNNEST(tags) AS tag;

-- 数组函数
SELECT ARRAY(
    SELECT x * 2 FROM UNNEST([1, 2, 3]) AS x
); -- [2, 4, 6]

-- 数组排序
SELECT ARRAY(
    SELECT x FROM UNNEST([3, 1, 2]) AS x ORDER BY x
); -- [1, 2, 3]

-- 结构体类型，存储命名字段集合
-- 常用于查询结果和嵌套数据

-- 创建结构体
SELECT STRUCT('John' AS name, 30 AS age);
-- {name: 'John', age: 30}

-- 结构体数组
SELECT ARRAY(
    SELECT AS STRUCT name, age FROM users
);

-- 在表中使用结构体
CREATE TABLE orders (
    order_id INT64 NOT NULL,
    customer STRUCT<id INT64, name STRING(100)>,
    items ARRAY<STRUCT<
        product_id INT64,
        quantity INT64,
        price FLOAT64
    >>
) PRIMARY KEY (order_id);

-- 插入数据
INSERT INTO orders (order_id, customer, items)
VALUES (
    1,
    STRUCT<id INT64, name STRING(100)>(1, 'John'),
    [
        STRUCT<product_id INT64, quantity INT64, price FLOAT64>(101, 2, 9.99),
        STRUCT<product_id INT64, quantity INT64, price FLOAT64>(102, 1, 19.99)
    ]
);

-- 访问结构体字段
SELECT customer.name FROM orders WHERE order_id = 1;

-- 展开结构体数组
SELECT 
    order_id,
    item.product_id,
    item.quantity,
    item.price
FROM orders, UNNEST(items) AS item;

-- 显式类型转换
SELECT CAST('123' AS INT64);
SELECT CAST(123 AS STRING);
SELECT CAST('2024-03-27' AS DATE);
SELECT CAST(3.14 AS INT64);    -- 截断为3

-- 转换失败会报错
SELECT CAST('abc' AS INT64);   -- ERROR

-- 安全转换，失败返回NULL
SELECT SAFE_CAST('abc' AS INT64);  -- NULL
SELECT SAFE_CAST('123' AS INT64);  -- 123

-- 配合COALESCE使用
SELECT COALESCE(SAFE_CAST('abc' AS INT64), 0); -- 0

-- NULL表示缺失或未知值
SELECT NULL;
SELECT NULL::INT64;

-- NULL与任何值运算结果为NULL
SELECT NULL + 1;               -- NULL
SELECT NULL = NULL;            -- NULL
SELECT NULL != 1;              -- NULL

-- COALESCE: 返回第一个非NULL值
SELECT COALESCE(NULL, NULL, 'default'); -- 'default'

-- IFNULL: 如果为NULL则返回默认值
SELECT IFNULL(name, 'Unknown') FROM users;

-- NULLIF: 如果相等则返回NULL
SELECT NULLIF(status, 'inactive'); -- 如果status='inactive'返回NULL

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

1. 选择合适的类型
   └── 避免使用STRING(MAX)存储固定长度数据

2. 合理设置长度
   └── STRING(n)比STRING(MAX)更高效

3. 使用ARRAY存储列表
   └── 避免创建关联表

4. 使用JSON存储灵活数据
   └── 不确定Schema时使用

5. 处理NULL值
   └── 使用COALESCE或IFNULL提供默认值