-- ============================================= -- CRM 客户新增经销商备案相关字段 -- 目标库:mom-thzy -- 说明:MySQL 的 ALTER TABLE ADD COLUMN 不支持 IF NOT EXISTS, -- 先查 information_schema 再执行,保证脚本可重复执行。 -- ============================================= SET @col_exists = ( SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_customer' AND column_name = 'social_credit_code' ); SET @ddl = IF(@col_exists = 0, 'ALTER TABLE `crm_customer` ADD COLUMN `social_credit_code` VARCHAR(64) NULL COMMENT ''社会信用代码'' AFTER `name`, ADD COLUMN `id_card_no` VARCHAR(32) NULL COMMENT ''身份证号'' AFTER `social_credit_code`, ADD COLUMN `customer_name` VARCHAR(64) NULL COMMENT ''客户姓名'' AFTER `id_card_no`, ADD COLUMN `filing_serial_no` VARCHAR(64) NULL COMMENT ''备案流水号'' AFTER `customer_name`, ADD COLUMN `bank_account_name` VARCHAR(128) NULL COMMENT ''银行账户名称'' AFTER `filing_serial_no`, ADD COLUMN `region` VARCHAR(64) NULL COMMENT ''所属区域'' AFTER `bank_account_name`, ADD COLUMN `parent_dealer_id` BIGINT NULL COMMENT ''所属上级经销商编号'' AFTER `region`', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 索引同样用 information_schema 判断(MySQL 8.4 的 ADD INDEX IF NOT EXISTS 不可用) SET @idx_exists = ( SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'crm_customer' AND index_name = 'idx_crm_customer_parent_dealer_id' ); SET @ddl = IF(@idx_exists = 0, 'ALTER TABLE `crm_customer` ADD INDEX `idx_crm_customer_parent_dealer_id` (`parent_dealer_id`)', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @idx_exists = ( SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'crm_customer' AND index_name = 'idx_crm_customer_social_credit_code' ); SET @ddl = IF(@idx_exists = 0, 'ALTER TABLE `crm_customer` ADD INDEX `idx_crm_customer_social_credit_code` (`social_credit_code`)', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;