Tag Archive audit

SELECT 
    CONCAT(
        'SELECT ''', T.Real_Table_Name, ''' AS table_Name,',
        T.PK_Col, ''' AS PK_column,',
        'COALESCE(MAX(`', T.PK_Col, '`), 0) AS max_existing_id, ',
        T.System_AutoInc, ' AS running_auto_increment, ',
        -- ส่วนเช็คเงื่อนไขและสร้างคำสั่งแก้
        'CASE WHEN ', T.System_AutoInc, ' <= COALESCE(MAX(`', T.PK_Col, '`), 0) ',
        'THEN CONCAT(''ALTER TABLE `', T.Real_Table_Name, '` AUTO_INCREMENT = '', MAX(`', T.PK_Col, '`) + 1, '';'') ',
        'ELSE '''' END AS SQL_Fix_Command ',

        'FROM `', T.Real_Table_Name, '` UNION ALL '
    ) as Copy_This_Code
FROM (
    SELECT 
        t.TABLE_NAME as Real_Table_Name,
        t.AUTO_INCREMENT as System_AutoInc,
        k.COLUMN_NAME as PK_Col
    FROM information_schema.TABLES t
    JOIN information_schema.KEY_COLUMN_USAGE k 
        ON t.TABLE_SCHEMA = k.TABLE_SCHEMA 
        AND t.TABLE_NAME = k.TABLE_NAME
    WHERE t.TABLE_SCHEMA = DATABASE()
      AND t.AUTO_INCREMENT IS NOT NULL
      AND k.CONSTRAINT_NAME = 'PRIMARY'

      -- กรองเฉพาะตารางที่มีคำว่า 'wp_' (หรือเปลี่ยนเป็น '%' เพื่อเอาทั้งหมด)
      AND t.TABLE_NAME LIKE '%wp_%' 

    GROUP BY t.TABLE_NAME
    HAVING COUNT(k.COLUMN_NAME) = 1
) AS T;

SELECT 'wp_commentmeta' AS table_Name, 'meta_id' AS PK_column, COALESCE(MAX(`meta_id`), 0) AS max_existing_id, 4 AS current_auto_increment, CASE WHEN 4 <= COALESCE(MAX(`meta_id`), 0) THEN CONCAT('ALTER TABLE `wp_commentmeta` AUTO_INCREMENT = ', MAX(`meta_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_commentmeta` UNION ALL 
SELECT 'wp_comments' AS table_Name, 'comment_ID' AS PK_column, COALESCE(MAX(`comment_ID`), 0) AS max_existing_id, 288 AS current_auto_increment, CASE WHEN 288 <= COALESCE(MAX(`comment_ID`), 0) THEN CONCAT('ALTER TABLE `wp_comments` AUTO_INCREMENT = ', MAX(`comment_ID`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_comments` UNION ALL 
SELECT 'wp_links' AS table_Name, 'link_id' AS PK_column, COALESCE(MAX(`link_id`), 0) AS max_existing_id, 1 AS current_auto_increment, CASE WHEN 1 <= COALESCE(MAX(`link_id`), 0) THEN CONCAT('ALTER TABLE `wp_links` AUTO_INCREMENT = ', MAX(`link_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_links` UNION ALL 
SELECT 'wp_options' AS table_Name, 'option_id' AS PK_column, COALESCE(MAX(`option_id`), 0) AS max_existing_id, 1183460 AS current_auto_increment, CASE WHEN 1183460 <= COALESCE(MAX(`option_id`), 0) THEN CONCAT('ALTER TABLE `wp_options` AUTO_INCREMENT = ', MAX(`option_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_options` UNION ALL 
SELECT 'wp_postmeta' AS table_Name, 'meta_id' AS PK_column, COALESCE(MAX(`meta_id`), 0) AS max_existing_id, 69206 AS current_auto_increment, CASE WHEN 69206 <= COALESCE(MAX(`meta_id`), 0) THEN CONCAT('ALTER TABLE `wp_postmeta` AUTO_INCREMENT = ', MAX(`meta_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_postmeta` UNION ALL 
SELECT 'wp_posts' AS table_Name, 'ID' AS PK_column, COALESCE(MAX(`ID`), 0) AS max_existing_id, 8322 AS current_auto_increment, CASE WHEN 8322 <= COALESCE(MAX(`ID`), 0) THEN CONCAT('ALTER TABLE `wp_posts` AUTO_INCREMENT = ', MAX(`ID`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_posts` UNION ALL 
SELECT 'wp_termmeta' AS table_Name, 'meta_id' AS PK_column, COALESCE(MAX(`meta_id`), 0) AS max_existing_id, 126 AS current_auto_increment, CASE WHEN 126 <= COALESCE(MAX(`meta_id`), 0) THEN CONCAT('ALTER TABLE `wp_termmeta` AUTO_INCREMENT = ', MAX(`meta_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_termmeta` UNION ALL 
SELECT 'wp_terms' AS table_Name, 'term_id' AS PK_column, COALESCE(MAX(`term_id`), 0) AS max_existing_id, 1990 AS current_auto_increment, CASE WHEN 1990 <= COALESCE(MAX(`term_id`), 0) THEN CONCAT('ALTER TABLE `wp_terms` AUTO_INCREMENT = ', MAX(`term_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_terms` UNION ALL 
SELECT 'wp_term_taxonomy' AS table_Name, 'term_taxonomy_id' AS PK_column, COALESCE(MAX(`term_taxonomy_id`), 0) AS max_existing_id, 3833 AS current_auto_increment, CASE WHEN 3833 <= COALESCE(MAX(`term_taxonomy_id`), 0) THEN CONCAT('ALTER TABLE `wp_term_taxonomy` AUTO_INCREMENT = ', MAX(`term_taxonomy_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_term_taxonomy` UNION ALL 
SELECT 'wp_usermeta' AS table_Name, 'umeta_id' AS PK_column, COALESCE(MAX(`umeta_id`), 0) AS max_existing_id, 71 AS current_auto_increment, CASE WHEN 71 <= COALESCE(MAX(`umeta_id`), 0) THEN CONCAT('ALTER TABLE `wp_usermeta` AUTO_INCREMENT = ', MAX(`umeta_id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_usermeta` UNION ALL 
SELECT 'wp_users' AS table_Name, 'ID' AS PK_column, COALESCE(MAX(`ID`), 0) AS max_existing_id, 3 AS current_auto_increment, CASE WHEN 3 <= COALESCE(MAX(`ID`), 0) THEN CONCAT('ALTER TABLE `wp_users` AUTO_INCREMENT = ', MAX(`ID`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_users` UNION ALL 
SELECT 'wp_yoast_indexable' AS table_Name, 'id' AS PK_column, COALESCE(MAX(`id`), 0) AS max_existing_id, 2431 AS current_auto_increment, CASE WHEN 2431 <= COALESCE(MAX(`id`), 0) THEN CONCAT('ALTER TABLE `wp_yoast_indexable` AUTO_INCREMENT = ', MAX(`id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_yoast_indexable` UNION ALL 
SELECT 'wp_yoast_migrations' AS table_Name, 'id' AS PK_column, COALESCE(MAX(`id`), 0) AS max_existing_id, 25 AS current_auto_increment, CASE WHEN 25 <= COALESCE(MAX(`id`), 0) THEN CONCAT('ALTER TABLE `wp_yoast_migrations` AUTO_INCREMENT = ', MAX(`id`) + 1, ';') ELSE '' END AS SQL_Fix_Command FROM `wp_yoast_migrations` UNION ALL