将视图转为表

SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY,
COLUMN_DEFAULT,
EXTRA
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_view_name';

 

-- 假设你已经替换了'your_database_name'和'your_view_name'为实际的数据库名和视图名
SELECT
CONCAT(
'CREATE TABLE ao_capacity_it_view_table (',
GROUP_CONCAT(
CONCAT(
COLUMN_NAME, ' ', DATA_TYPE,
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND DATA_TYPE IN ('varchar', 'char', 'text', 'binary', 'varbinary') THEN CONCAT('(', CHARACTER_MAXIMUM_LENGTH, ')')
WHEN DATA_TYPE = 'decimal' AND NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL THEN CONCAT('(', NUMERIC_PRECISION, ',', NUMERIC_SCALE, ')')
ELSE ''
END,
CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN CONCAT(' DEFAULT ', COLUMN_DEFAULT) ELSE '' END,
CASE WHEN EXTRA LIKE '%auto_increment%' THEN ' AUTO_INCREMENT' ELSE '' END
),
', '
),
');'
) AS create_table_sql
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_view_name';