入职资料_银行实名与身份证地址照片.sql
4.85 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
-- =============================================================================
-- 入职资料：银行开户实名、身份证地址与身份证正反面照片
-- 说明：为 lq_employee_profile / lq_employee_profile_submission 补齐字段；可重复执行
-- =============================================================================
SET NAMES utf8mb4;
SET @db_name = DATABASE();
-- lq_employee_profile.F_BankAccountName
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile' AND COLUMN_NAME = 'F_BankAccountName'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile` ADD COLUMN `F_BankAccountName` varchar(128) DEFAULT NULL COMMENT ''开户实名（开户人真实姓名）'' AFTER `F_BankCardCipher`;',
'SELECT ''lq_employee_profile.F_BankAccountName 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile.F_IdCardAddress
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile' AND COLUMN_NAME = 'F_IdCardAddress'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile` ADD COLUMN `F_IdCardAddress` varchar(500) DEFAULT NULL COMMENT ''身份证地址（完整地址，独立于籍贯）'' AFTER `F_BankAccountName`;',
'SELECT ''lq_employee_profile.F_IdCardAddress 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile.F_IdCardFrontUrl
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile' AND COLUMN_NAME = 'F_IdCardFrontUrl'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile` ADD COLUMN `F_IdCardFrontUrl` varchar(500) DEFAULT NULL COMMENT ''身份证正面照片 URL'' AFTER `F_IdCardAddress`;',
'SELECT ''lq_employee_profile.F_IdCardFrontUrl 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile.F_IdCardBackUrl
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile' AND COLUMN_NAME = 'F_IdCardBackUrl'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile` ADD COLUMN `F_IdCardBackUrl` varchar(500) DEFAULT NULL COMMENT ''身份证反面照片 URL'' AFTER `F_IdCardFrontUrl`;',
'SELECT ''lq_employee_profile.F_IdCardBackUrl 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile_submission.F_BankAccountName
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile_submission' AND COLUMN_NAME = 'F_BankAccountName'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile_submission` ADD COLUMN `F_BankAccountName` varchar(128) DEFAULT NULL COMMENT ''开户实名（开户人真实姓名）'' AFTER `F_BankCardCipher`;',
'SELECT ''lq_employee_profile_submission.F_BankAccountName 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile_submission.F_IdCardAddress
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile_submission' AND COLUMN_NAME = 'F_IdCardAddress'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile_submission` ADD COLUMN `F_IdCardAddress` varchar(500) DEFAULT NULL COMMENT ''身份证地址（完整地址，独立于籍贯）'' AFTER `F_BankAccountName`;',
'SELECT ''lq_employee_profile_submission.F_IdCardAddress 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile_submission.F_IdCardFrontUrl
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile_submission' AND COLUMN_NAME = 'F_IdCardFrontUrl'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile_submission` ADD COLUMN `F_IdCardFrontUrl` varchar(500) DEFAULT NULL COMMENT ''身份证正面照片 URL'' AFTER `F_IdCardAddress`;',
'SELECT ''lq_employee_profile_submission.F_IdCardFrontUrl 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- lq_employee_profile_submission.F_IdCardBackUrl
SET @col_exists = (
SELECT COUNT(1) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_employee_profile_submission' AND COLUMN_NAME = 'F_IdCardBackUrl'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE `lq_employee_profile_submission` ADD COLUMN `F_IdCardBackUrl` varchar(500) DEFAULT NULL COMMENT ''身份证反面照片 URL'' AFTER `F_IdCardFrontUrl`;',
'SELECT ''lq_employee_profile_submission.F_IdCardBackUrl 已存在'';'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;