반응형
InnoDB가 테이블 / 테이블에서 외래 키를 다시 확인하도록 강제합니까?
InnoDB일부 행을 제거하고 다른 행을 삽입하여 주기적으로 유지 관리해야하는 테이블 세트가 있습니다. 일부 테이블에는 다른 테이블을 참조하는 외래 키 제약 조건이 있으므로 테이블로드 순서가 중요합니다. 테이블 순서에 대해 걱정하지 않고 새 행을 삽입하려면 다음을 사용합니다.
SET FOREIGN_KEY_CHECKS=0;
이전 및 이후 :
SET FOREIGN_KEY_CHECKS=1;
후.
로드가 완료되면 업데이트 된 테이블의 데이터가 여전히 참조 무결성을 유지하는지 확인하고 싶습니다. 새 행이 외래 키 제약 조건을 위반하지 않는지 확인하고 싶지만 이렇게 할 방법이없는 것 같습니다.
테스트로 외래 키 제약 조건을 확실히 위반 한 데이터를 입력했으며 외래 키 검사를 다시 활성화 할 때 mysql은 경고 나 오류를 생성하지 않았습니다.
테이블로드 순서를 지정하는 방법을 찾으려고로드 프로세스 중에 외래 키 검사를 그대로두면 자체 참조 외래 키 제약 조건이있는 테이블에 데이터를로드 할 수 없습니다. 수용 가능한 솔루션이 아닙니다.
InnoDB가 테이블 또는 데이터베이스의 외래 키 제약 조건을 확인하도록 강제하는 방법이 있습니까?
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';');
END IF;
DEALLOCATE PREPARE stmt;
END LOOP foreign_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
이 저장 프로 시저를 사용하여 모든 데이터베이스에서 잘못된 외래 키를 확인할 수 있습니다. 결과는 INVALID_FOREIGN_KEYS테이블에 로드됩니다 . 매개 변수 ANALYZE_INVALID_FOREIGN_KEYS:
- 데이터베이스 이름 패턴 (LIKE 스타일)
- 테이블 이름 패턴 (LIKE 스타일)
결과가 일시적인지 여부.
'Y',,'N'일 수 있습니다NULL.- 의 경우 결과 테이블 임시 테이블이 될 것입니다. 다른 세션에서는 임시 테이블이 표시되지 않습니다. 임시 결과 테이블을 사용 하여 여러 저장 프로 시저를 병렬로 실행할 수 있습니다 .
'Y'ANALYZE_INVALID_FOREIGN_KEYSANALYZE_INVALID_FOREIGN_KEYS(...) - 그러나 다른 세션의 부분 결과에 관심이있는 경우를 사용한
'N'다음SELECT * FROM INVALID_FOREIGN_KEYS;다른 세션에서 실행 해야합니다 . NULLMySQL은CREATE TABLE ...및DROP TABLE ...에 대한 트랜잭션에서 암시 적 커밋을 실행하기 때문에 트랜잭션에서 결과 테이블 생성을 건너 뛰기 위해 를 사용해야합니다. 따라서 결과 테이블 생성은 트랜잭션에서 문제를 일으킬 수 있습니다. 이 경우BEGIN; COMMIT/ROLLBACK;블록 외부에서 직접 결과 테이블을 작성해야합니다 .CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) );암시 적 커밋에 대한 MySQL 사이트를 방문하십시오. http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
- 의 경우 결과 테이블 임시 테이블이 될 것입니다. 다른 세션에서는 임시 테이블이 표시되지 않습니다. 임시 결과 테이블을 사용 하여 여러 저장 프로 시저를 병렬로 실행할 수 있습니다 .
INVALID_FOREIGN_KEYS행은 유효하지 않은 데이터베이스, 테이블, 컬럼의 이름 만 포함됩니다. 하지만 만약 존재한다면 INVALID_KEY_SQL열의 값을 실행하면 유효하지 않은 참조 행을 볼 수 있습니다 INVALID_FOREIGN_KEYS.
이 저장 프로시 저는 참조 열 (일명 외부 색인)과 참조 열 (일반적으로 기본 키)에 색인이있는 경우 매우 빠릅니다.
이 훌륭한 답변에 감사드립니다-이것은 매우 편리한 도구입니다. 다음은 유효하지 않은 키가있는 키를 삭제하기 위해 출력 테이블에 SQL을 포함하는 약간 수정 된 버전의 프로 시저입니다. 이러한 행이 누락 / 비활성화 된 삭제 캐스케이드 규칙에서 고아임을 확인한 경우에 유용합니다 (기본에서 고아가 아님). 주요 변경 또는 기타 더 복잡한 경우).
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
END IF;
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';'),
`INVALID_KEY_DELETE_SQL` = CONCAT('DELETE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '` ',
'FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' ',
'ON (', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' = ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL', ';');
END IF;
DEALLOCATE PREPARE stmt;
END LOOP foreign_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
여러 열 외래 키를 처리하도록 스크립트를 수정했습니다.
CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(IN `checked_database_name` VARCHAR(64), IN `checked_table_name` VARCHAR(64), IN `temporary_result_table` ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;
SET @prev_name = '';
SET @from_part = '';
SET @where_part = '';
SET @where_nullable = '';
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
IF (@prev_name <> CONSTRAINT_NAME_VAR AND @from_part <> '' AND @where_part <> '') THEN
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = NULL,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = NULL,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;
SET @where_part = '';
SET @where_nullable = '';
END IF;
IF (LENGTH(@where_part) > 0) THEN
SET @where_nullable = CONCAT(@where_nullable, ' OR ');
SET @where_part = CONCAT(@where_part, ' AND ');
ELSE
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`',
TABLE_NAME_VAR, '`', ' AS REFERRING ');
SET @from_where_part = CONCAT('NOT EXISTS (SELECT * FROM `',
REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ');
END IF;
SET @where_nullable = CONCAT(@where_nullable, 'REFERRING.', COLUMN_NAME_VAR, ' IS NOT NULL');
SET @where_part = CONCAT(@where_part, 'REFERRING.', COLUMN_NAME_VAR, ' = ', 'REFERRED.', REFERENCED_COLUMN_NAME_VAR);
SET @prev_name = CONSTRAINT_NAME_VAR;
END LOOP foreign_key_cursor_loop;
IF (@where_part <> '' AND @from_part <> '') THEN
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = NULL,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = NULL,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;
END IF;
END
동일한 검사이지만 유효하지 않은 고유 키 분석 :
-> 작은 버그 / 기능 : 중복 null도보고합니다. (mysql은 중복 null을 허용하지만).
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_UNIQUE_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAMES_VAR VARCHAR(1000);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE unique_key_cursor CURSOR FOR
select kcu.table_schema sch,
kcu.table_name tbl,
group_concat(kcu.column_name) colName,
kcu.constraint_name constName
from
information_schema.table_constraints tc
join
information_schema.key_column_usage kcu
on
kcu.constraint_name=tc.constraint_name
and kcu.constraint_schema=tc.constraint_schema
and kcu.table_name=tc.table_name
where
kcu.table_schema like checked_database_name
and kcu.table_name like checked_table_name
and tc.constraint_type="UNIQUE" group by sch, tbl, constName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS INVALID_UNIQUE_KEYS;
CREATE TEMPORARY TABLE INVALID_UNIQUE_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAMES` VARCHAR(1000),
`CONSTRAINT_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT
);
OPEN unique_key_cursor;
unique_key_cursor_loop: LOOP
FETCH unique_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAMES_VAR,
CONSTRAINT_NAME_VAR;
IF done THEN
LEAVE unique_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM (SELECT COUNT(*) counter FROM', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`',
' GROUP BY ', COLUMN_NAMES_VAR , ') as s where s.counter > 1');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_UNIQUE_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAMES` = COLUMN_NAMES_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count;
END IF;
DEALLOCATE PREPARE stmt;
END LOOP unique_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_UNIQUE_KEYS('%', '%');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS;
SELECT * FROM INVALID_UNIQUE_KEYS;
그렇게 할 수있는 도구는 없습니다. 그러나 모든 테이블을 살펴보고 외래 키 제한을 삭제하고 다시 만드는 스크립트를 작성할 수 있습니다. 레크리에이션에서 뭔가 잘못되면 오류가 발생합니다.
참고 URL : https://stackoverflow.com/questions/2250775/force-innodb-to-recheck-foreign-keys-on-a-table-tables
반응형
'Program Tip' 카테고리의 다른 글
| C # 코드에서 wpf의 ResourceDictionary에 어떻게 액세스 할 수 있습니까? (0) | 2020.12.11 |
|---|---|
| Html Agility Pack은 여전히 최고의 .NET HTML 파서입니까? (0) | 2020.12.11 |
| sed 특정 문자열을 포함하지 않는 줄 삭제 (0) | 2020.12.11 |
| JSONException : java.lang.String 유형의 값을 JSONObject로 변환 할 수 없습니다. (0) | 2020.12.11 |
| Pandas to_csv가있는 float64 (0) | 2020.12.11 |