ScyllaDB数据类型 #

ScyllaDB数据类型
├── 基本类型
│   ├── 数值类型
│   ├── 文本类型
│   ├── 时间类型
│   └── 其他类型
├── 集合类型
│   ├── List
│   ├── Set
│   └── Map
├── 用户定义类型
│   └── UDT
└── 特殊类型
    ├── Counter
    ├── Tuple
    └── Frozen

-- 创建表
CREATE TABLE numbers (
    id INT PRIMARY KEY,
    tiny_val TINYINT,
    small_val SMALLINT,
    int_val INT,
    big_val BIGINT,
    var_val VARINT
);

-- 插入数据
INSERT INTO numbers (id, tiny_val, small_val, int_val, big_val, var_val)
VALUES (1, 127, 32767, 2147483647, 9223372036854775807, 123456789012345678901234567890);

-- 创建表
CREATE TABLE floats (
    id INT PRIMARY KEY,
    float_val FLOAT,
    double_val DOUBLE,
    decimal_val DECIMAL
);

-- 插入数据
INSERT INTO floats (id, float_val, double_val, decimal_val)
VALUES (1, 3.14159, 3.14159265358979, 3.141592653589793238462643383279);

-- 创建表
CREATE TABLE texts (
    id INT PRIMARY KEY,
    ascii_text ASCII,
    utf8_text TEXT,
    varchar_text VARCHAR
);

-- 插入数据
INSERT INTO texts (id, ascii_text, utf8_text, varchar_text)
VALUES (1, 'Hello', '你好世界', 'こんにちは');

-- 注意：ascii类型只能存储ASCII字符
-- 以下会报错：
-- INSERT INTO texts (id, ascii_text) VALUES (2, '你好');

-- 创建表
CREATE TABLE events (
    id INT PRIMARY KEY,
    event_time TIMESTAMP,
    event_date DATE,
    event_duration TIME,
    duration_val DURATION
);

-- 插入数据
INSERT INTO events (id, event_time, event_date, event_duration, duration_val)
VALUES (
    1, 
    '2024-01-15 10:30:00', 
    '2024-01-15', 
    '10:30:00.123',
    1y2mo3d
);

-- 使用时间函数
INSERT INTO events (id, event_time, event_date)
VALUES (2, toTimestamp(now()), toDate(now()));

-- 创建带时间戳的表
CREATE TABLE time_series (
    sensor_id INT,
    reading_time TIMESTAMP,
    value DOUBLE,
    PRIMARY KEY (sensor_id, reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);

-- 插入时间戳数据
INSERT INTO time_series (sensor_id, reading_time, value)
VALUES (1, '2024-01-15 10:00:00', 25.5);

INSERT INTO time_series (sensor_id, reading_time, value)
VALUES (1, '2024-01-15 10:01:00', 25.7);

-- 查询时间范围
SELECT * FROM time_series
WHERE sensor_id = 1 
  AND reading_time >= '2024-01-15 10:00:00'
  AND reading_time < '2024-01-15 11:00:00';

-- 创建表
CREATE TABLE uuid_example (
    id UUID PRIMARY KEY,
    time_id TIMEUUID,
    name TEXT
);

-- 插入UUID
INSERT INTO uuid_example (id, time_id, name)
VALUES (uuid(), now(), '张三');

-- 查询
SELECT * FROM uuid_example;

-- TimeUUID函数
SELECT now();           -- 生成当前时间的timeuuid
SELECT minTimeuuid('2024-01-15 10:30:00');  -- 最小timeuuid
SELECT maxTimeuuid('2024-01-15 10:30:00');  -- 最大timeuuid

-- 创建按时间排序的表
CREATE TABLE events_by_time (
    event_type TEXT,
    event_id TIMEUUID,
    data TEXT,
    PRIMARY KEY (event_type, event_id)
) WITH CLUSTERING ORDER BY (event_id DESC);

-- 插入事件
INSERT INTO events_by_time (event_type, event_id, data)
VALUES ('login', now(), '用户登录');

INSERT INTO events_by_time (event_type, event_id, data)
VALUES ('login', now(), '用户登录2');

-- 查询最新事件
SELECT * FROM events_by_time 
WHERE event_type = 'login' 
LIMIT 10;

-- 创建表
CREATE TABLE flags (
    id INT PRIMARY KEY,
    is_active BOOLEAN,
    is_verified BOOLEAN
);

-- 插入数据
INSERT INTO flags (id, is_active, is_verified)
VALUES (1, true, false);

INSERT INTO flags (id, is_active, is_verified)
VALUES (2, FALSE, TRUE);

-- 查询
SELECT * FROM flags WHERE is_active = true;

-- 创建表
CREATE TABLE binary_data (
    id INT PRIMARY KEY,
    data BLOB
);

-- 插入十六进制数据
INSERT INTO binary_data (id, data)
VALUES (1, 0x48656c6c6f);

-- 插入文本转换的blob
INSERT INTO binary_data (id, data)
VALUES (2, textAsBlob('Hello'));

-- 查询并转换
SELECT id, blobAsText(data) FROM binary_data;

-- 创建表
CREATE TABLE network_events (
    id INT PRIMARY KEY,
    source_ip INET,
    dest_ip INET
);

-- 插入IPv4地址
INSERT INTO network_events (id, source_ip, dest_ip)
VALUES (1, '192.168.1.1', '10.0.0.1');

-- 插入IPv6地址
INSERT INTO network_events (id, source_ip, dest_ip)
VALUES (2, '2001:db8::1', '2001:db8::2');

-- 查询
SELECT * FROM network_events WHERE source_ip = '192.168.1.1';

-- 创建带List的表
CREATE TABLE users_with_list (
    user_id UUID PRIMARY KEY,
    name TEXT,
    tags LIST<TEXT>,
    scores LIST<INT>
);

-- 插入数据
INSERT INTO users_with_list (user_id, name, tags, scores)
VALUES (
    uuid(), 
    '张三', 
    ['vip', 'premium', 'active'], 
    [90, 85, 92]
);

-- 查询
SELECT * FROM users_with_list;

-- 追加元素
UPDATE users_with_list 
SET tags = tags + ['new_tag'] 
WHERE user_id = ?;

-- 前置插入
UPDATE users_with_list 
SET tags = ['first'] + tags 
WHERE user_id = ?;

-- 替换特定位置
UPDATE users_with_list 
SET tags[1] = 'updated_tag' 
WHERE user_id = ?;

-- 删除元素
UPDATE users_with_list 
SET tags = tags - ['vip'] 
WHERE user_id = ?;

-- 删除特定位置
DELETE tags[0] FROM users_with_list WHERE user_id = ?;

-- 查询包含某元素
SELECT * FROM users_with_list WHERE tags CONTAINS 'vip';

-- 创建带Set的表
CREATE TABLE users_with_set (
    user_id UUID PRIMARY KEY,
    name TEXT,
    emails SET<TEXT>,
    tags SET<TEXT>
);

-- 插入数据
INSERT INTO users_with_set (user_id, name, emails, tags)
VALUES (
    uuid(), 
    '张三', 
    {'zhangsan@example.com', 'zhangsan@work.com'}, 
    {'vip', 'premium'}
);

-- 添加元素
UPDATE users_with_set 
SET emails = emails + {'new@example.com'} 
WHERE user_id = ?;

-- 删除元素
UPDATE users_with_set 
SET emails = emails - {'old@example.com'} 
WHERE user_id = ?;

-- 查询包含某元素
SELECT * FROM users_with_set WHERE emails CONTAINS 'zhangsan@example.com';

-- 查询包含所有元素
SELECT * FROM users_with_set WHERE tags CONTAINS 'vip' AND tags CONTAINS 'premium';

-- 创建带Map的表
CREATE TABLE users_with_map (
    user_id UUID PRIMARY KEY,
    name TEXT,
    attributes MAP<TEXT, TEXT>,
    scores MAP<TEXT, INT>
);

-- 插入数据
INSERT INTO users_with_map (user_id, name, attributes, scores)
VALUES (
    uuid(), 
    '张三', 
    {'phone': '13800138000', 'city': '北京', 'country': '中国'}, 
    {'math': 90, 'english': 85, 'science': 92}
);

-- 添加键值对
UPDATE users_with_map 
SET attributes = attributes + {'email': 'new@example.com'} 
WHERE user_id = ?;

-- 更新特定键
UPDATE users_with_map 
SET attributes['phone'] = '13900139000' 
WHERE user_id = ?;

-- 删除键
DELETE attributes['email'] FROM users_with_map WHERE user_id = ?;

-- 查询包含某键
SELECT * FROM users_with_map WHERE attributes CONTAINS KEY 'phone';

-- 查询包含某值
SELECT * FROM users_with_map WHERE attributes CONTAINS '北京';

-- 查询特定键值
SELECT user_id, name, attributes['phone'] AS phone FROM users_with_map;

-- 创建地址类型
CREATE TYPE address (
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    country TEXT
);

-- 创建联系方式类型
CREATE TYPE contact_info (
    email TEXT,
    phone TEXT,
    address FROZEN<address>
);

-- 使用UDT创建表
CREATE TABLE users_with_udt (
    user_id UUID PRIMARY KEY,
    name TEXT,
    home_address FROZEN<address>,
    work_address FROZEN<address>,
    contact FROZEN<contact_info>
);

-- 插入嵌套UDT
INSERT INTO users_with_udt (user_id, name, home_address, work_address)
VALUES (
    uuid(),
    '张三',
    {
        street: '朝阳路100号',
        city: '北京',
        state: '北京市',
        zip_code: '100020',
        country: '中国'
    },
    {
        street: '中关村大街1号',
        city: '北京',
        state: '北京市',
        zip_code: '100080',
        country: '中国'
    }
);

-- 插入包含嵌套UDT的数据
INSERT INTO users_with_udt (user_id, name, contact)
VALUES (
    uuid(),
    '李四',
    {
        email: 'lisi@example.com',
        phone: '13800138000',
        address: {
            street: '南京路200号',
            city: '上海',
            state: '上海市',
            zip_code: '200000',
            country: '中国'
        }
    }
);

-- 查询完整UDT
SELECT user_id, name, home_address FROM users_with_udt;

-- 查询UDT字段
SELECT user_id, name, home_address.city, home_address.zip_code 
FROM users_with_udt;

-- 查询嵌套UDT字段
SELECT user_id, name, contact.address.city 
FROM users_with_udt;

-- 使用WHERE条件
SELECT * FROM users_with_udt 
WHERE home_address.city = '北京';

-- 更新整个UDT
UPDATE users_with_udt 
SET home_address = {
    street: '新地址',
    city: '上海',
    state: '上海市',
    zip_code: '200000',
    country: '中国'
}
WHERE user_id = ?;

-- 更新UDT字段
UPDATE users_with_udt 
SET home_address.city = '深圳' 
WHERE user_id = ?;

-- 创建包含UDT集合的表
CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    customer_name TEXT,
    items LIST<FROZEN<order_item>>
);

-- 创建订单项类型
CREATE TYPE order_item (
    product_id UUID,
    product_name TEXT,
    quantity INT,
    price DECIMAL
);

-- 插入数据
INSERT INTO orders (order_id, customer_name, items)
VALUES (
    uuid(),
    '张三',
    [
        {
            product_id: uuid(),
            product_name: 'iPhone 15',
            quantity: 1,
            price: 7999.00
        },
        {
            product_id: uuid(),
            product_name: 'AirPods Pro',
            quantity: 1,
            price: 1999.00
        }
    ]
);

-- 创建计数器表
CREATE TABLE page_views (
    page_id UUID PRIMARY KEY,
    view_count COUNTER
);

-- 创建多计数器表
CREATE TABLE user_stats (
    user_id UUID PRIMARY KEY,
    login_count COUNTER,
    post_count COUNTER,
    like_count COUNTER
);

-- 增加计数
UPDATE page_views SET view_count = view_count + 1 WHERE page_id = ?;

-- 减少计数
UPDATE page_views SET view_count = view_count - 1 WHERE page_id = ?;

-- 批量更新计数器
BEGIN BATCH
    UPDATE user_stats SET login_count = login_count + 1 WHERE user_id = ?;
    UPDATE user_stats SET post_count = post_count + 5 WHERE user_id = ?;
APPLY BATCH;

-- 创建带Tuple的表
CREATE TABLE coordinates (
    id INT PRIMARY KEY,
    point TUPLE<DOUBLE, DOUBLE>,
    data TUPLE<TEXT, INT, BOOLEAN>
);

-- 插入数据
INSERT INTO coordinates (id, point, data)
VALUES (1, (39.9042, 116.4074), ('北京', 1, true));

-- 查询Tuple字段
SELECT id, point, data FROM coordinates;

-- 查询特定Tuple元素
SELECT id, point.f1 AS latitude, point.f2 AS longitude 
FROM coordinates;

-- Frozen用于嵌套集合
CREATE TABLE nested_collections (
    id INT PRIMARY KEY,
    tags_map MAP<TEXT, FROZEN<LIST<TEXT>>>,
    nested_set SET<FROZEN<SET<INT>>>
);

-- 插入数据
INSERT INTO nested_collections (id, tags_map)
VALUES (1, {
    'category': ['tech', 'programming'],
    'level': ['beginner', 'intermediate']
});

-- Blob转换
SELECT blobAsInt(intAsBlob(123));
SELECT blobAsText(textAsBlob('Hello'));
SELECT blobAsBigint(bigintAsBlob(1234567890));

-- 时间转换
SELECT toTimestamp(now());
SELECT toDate(now());
SELECT toUnixTimestamp(now());

-- UUID转换
SELECT uuid();
SELECT now();

-- 从JSON创建
INSERT INTO users JSON '{
    "user_id": "123e4567-e89b-12d3-a456-426614174000",
    "name": "张三",
    "age": 30
}';

-- 输出为JSON
SELECT JSON * FROM users;