39 lines
1.2 KiB
SQL
39 lines
1.2 KiB
SQL
-- persons 表：补齐首页置顶与来源字段（兼容低版本 MySQL 的幂等写法）
|
||
SET @db = DATABASE();
|
||
|
||
SET @sql = (
|
||
SELECT IF(
|
||
EXISTS(
|
||
SELECT 1 FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'persons' AND COLUMN_NAME = 'is_pinned'
|
||
),
|
||
'SELECT 1',
|
||
"ALTER TABLE persons ADD COLUMN is_pinned TINYINT(1) NOT NULL DEFAULT 0 COMMENT '置顶到小程序首页'"
|
||
)
|
||
);
|
||
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @sql = (
|
||
SELECT IF(
|
||
EXISTS(
|
||
SELECT 1 FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'persons' AND COLUMN_NAME = 'person_source'
|
||
),
|
||
'SELECT 1',
|
||
"ALTER TABLE persons ADD COLUMN person_source VARCHAR(32) NOT NULL DEFAULT '' COMMENT '来源：空=后台手工；vip_sync=超级个体同步'"
|
||
)
|
||
);
|
||
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @sql = (
|
||
SELECT IF(
|
||
EXISTS(
|
||
SELECT 1 FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'persons' AND INDEX_NAME = 'idx_persons_is_pinned'
|
||
),
|
||
'SELECT 1',
|
||
'CREATE INDEX idx_persons_is_pinned ON persons(is_pinned)'
|
||
)
|
||
);
|
||
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|