-- Add VIN snapshot column to every physical charging-order shard.
|
-- Execute directly on the backend MySQL instance, not through the database proxy.
|
-- The procedure is idempotent and only alters existing tables that lack the column.
|
|
USE `charging_pile_order`;
|
|
DROP PROCEDURE IF EXISTS `add_charging_order_vin`;
|
DELIMITER $$
|
CREATE PROCEDURE `add_charging_order_vin`()
|
BEGIN
|
DECLARE shard_no INT DEFAULT 0;
|
DECLARE target_table VARCHAR(64);
|
DECLARE table_exists INT DEFAULT 0;
|
DECLARE column_exists INT DEFAULT 0;
|
|
WHILE shard_no < 50 DO
|
SET target_table = CONCAT('t_charging_order_', shard_no);
|
|
SELECT COUNT(*) INTO table_exists
|
FROM information_schema.tables
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'vin';
|
|
IF table_exists = 1 AND column_exists = 0 THEN
|
SET @ddl = CONCAT(
|
'ALTER TABLE `', target_table,
|
'` ADD COLUMN `vin` varchar(17) NULL AFTER `reservation_time`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SET shard_no = shard_no + 1;
|
END WHILE;
|
END$$
|
DELIMITER ;
|
|
CALL `add_charging_order_vin`();
|
DROP PROCEDURE `add_charging_order_vin`;
|
|
SELECT COUNT(*) AS shard_count,
|
SUM(CASE WHEN c.column_name = 'vin' THEN 1 ELSE 0 END) AS vin_column_count
|
FROM information_schema.tables t
|
LEFT JOIN information_schema.columns c
|
ON c.table_schema = t.table_schema
|
AND c.table_name = t.table_name
|
AND c.column_name = 'vin'
|
WHERE t.table_schema = DATABASE()
|
AND t.table_name REGEXP '^t_charging_order_[0-9]+$';
|
