programing

데이터베이스의 모든 테이블을 하나의 대조로 변환하려면 어떻게 해야 합니까?

itsource 2022. 10. 25. 21:49
반응형

데이터베이스의 모든 테이블을 하나의 대조로 변환하려면 어떻게 해야 합니까?

에러가 발생하고 있다.

잘못된 조합 혼합입니다(utf8_general_ci,IMPLICAT) 및 (utf8_unicode_ci,IMPLICAT) 작업 '='에 대해

두 테이블을 수동으로 변경해 보았습니다.utf8_general_ci,IMPLICIT에러가 계속 납니다.

모든 테이블을 변환하는 방법이 있습니까?utf8_general_ci,IMPLICIT끝내는 거야?

각 테이블에 대해 alter table 문을 실행해야 합니다.이 문장은 다음 양식을 따릅니다.

ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]

데이터베이스 내의 모든 테이블을 가져오려면 다음 쿼리를 실행해야 합니다.

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";

이제 MySQL이 코드를 작성하도록 하겠습니다.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";

결과를 복사하여 실행할 수 있습니다.구문을 테스트해 본 적은 없지만 나머지는 알 수 있을 겁니다.작은 운동이라고 생각하세요.

도움이 되길 바랍니다!

테이블 내 varchar 컬럼의 대조도 변경하는 것이 좋습니다.

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "myschema"
AND TABLE_TYPE="BASE TABLE"

번들 스크립트 사용을 시작하기 전에 utf8 이외의 열에 forein 키가 있는 데이터가 있는 경우 추가 정보

SET foreign_key_checks = 0;

즉, 글로벌 SQL은 mySQL용입니다.

SET foreign_key_checks = 0;
ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `tableXXX` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SET foreign_key_checks = 1;

단, mysql 설명서에 따르면 http://dev.mysql.com/doc/refman/5.1/en/charset-column.html,에 주의해 주십시오.

ALTER TABLE을 사용하여 한 문자 집합에서 다른 문자 집합으로 열을 변환하는 경우 MySQL은 데이터 값을 매핑하려고 하지만 문자 집합이 호환되지 않으면 데이터가 손실될 수 있습니다."

편집: 특히 컬럼유형 enum에서는 특별한 문자가 없어도 완전한 enums 집합이 됩니다.https://bugs.mysql.com/bug.php?id=26731

@남피비안의 제안이 나에게 큰 도움이 되었다...
조금 더 나아가서 컬럼과 뷰를 스크립트에 추가했습니다.

아래에 스키마 이름만 입력하면 나머지 작업이 수행됩니다.

-- set your table name here
SET @MY_SCHEMA = "";

-- tables
SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@MY_SCHEMA
  AND TABLE_TYPE="BASE TABLE"

UNION

-- table columns
SELECT DISTINCT
    CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
    AND C.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="BASE TABLE"

UNION

-- views
SELECT DISTINCT
    CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON V.TABLE_NAME = T.TABLE_NAME
WHERE V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";

다음은 보다 정확한 쿼리입니다.utf8로 변환하는 예를 제시하겠습니다.

SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="myschema"
AND TABLE_TYPE="BASE TABLE"

PhpMyAdmin을 사용하는 경우 다음을 수행할 수 있습니다.

  1. 데이터베이스를 선택합니다.
  2. [조작] 탭을 누릅니다.
  3. "조합" 섹션에서 원하는 조합을 선택합니다.
  4. "Change all tables collations" 체크박스를 클릭합니다.
  5. 새 "모든 테이블 열 정렬 변경" 확인란이 나타납니다.
  6. "Change all tables columns collections" 확인란을 누릅니다.
  7. "Go" 버튼을 클릭합니다.

변환할 테이블이 250개가 넘었어요5분 조금 넘게 걸렸어요.

다음의 BASH 스크립트를 사용할 수 있습니다.

#!/bin/bash

USER="YOUR_DATABASE_USER"
PASSWORD="YOUR_USER_PASSWORD"
DB_NAME="DATABASE_NAME"
CHARACTER_SET="utf8" # your default character set
COLLATE="utf8_general_ci" # your default collation

tables=`mysql -u $USER -p$PASSWORD -e "SELECT tbl.TABLE_NAME FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = '$DB_NAME' AND tbl.TABLE_TYPE='BASE TABLE'"`

for tableName in $tables; do
    if [[ "$tableName" != "TABLE_NAME" ]] ; then
        mysql -u $USER -p$PASSWORD -e "ALTER TABLE $DB_NAME.$tableName DEFAULT CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;"
        echo "$tableName - done"
    fi
done

phpMyAdmin의 경우 다음과 같은 사실을 알게 되었습니다.

SELECT GROUP_CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" SEPARATOR ' ') AS    OneSQLString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="yourtableschemaname"
AND TABLE_TYPE="BASE TABLE"

테이블쉐마네임만 바꾸면 괜찮아

이것이 내 버전의 bash 스크립트입니다.데이터베이스 이름을 매개 변수로 사용하여 모든 테이블을 다른 문자 집합 및 조합으로 변환합니다(스크립트에 정의된 다른 매개 변수 또는 기본값에 의해 지정됨).

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE $DB; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

패스워드 변수를 추가하여 @Petr Stastny의 답변을 한 단계 더 진전시킵니다.논쟁보다는 일반 비밀번호처럼 받아 들이는 게 더 낫지만, 제가 필요한 건 잘 되고 있어요.

#!/bin/bash

# mycollate.sh <database> <password> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
PW="$2"
CHARSET="$3"
COLL="$4"

[ -n "$DB" ] || exit 1
[ -n "$PW" ]
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_bin"

PW="--password=""$PW"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -u root "$PW"

echo "USE $DB; SHOW TABLES;" | mysql -s "$PW" | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$PW" $DB
    done
)

PW="pleaseEmptyMeNow"

GH에 이어 리모트 서버에서 이 작업을 수행해야 할 경우를 위해 사용자 및 호스트 파라미터를 추가했습니다.

    #!/bin/bash

    # mycollate.sh <database> <user> <password> [<host> <charset> <collation>]
    # changes MySQL/MariaDB charset and collation for one database - all tables and
    # all columns in all tables

    DB="$1"
    USER="$2"
    PW="$3"
    HOST="$4"
    CHARSET="$5"
    COLL="$6"

    [ -n "$DB" ] || exit 1
    [ -n "$USER" ] || exit 1
    [ -n "$PW" ] || exit 1
    [ -n "$HOST" ] || HOST="localhost"
    [ -n "$CHARSET" ] || CHARSET="utf8mb4"
    [ -n "$COLL" ] || COLL="utf8mb4_general_ci"

    PW="--password=""$PW"
    HOST="--host=""$HOST"
    USER="--user=""$USER"

    echo $DB
    echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$HOST" "$USER" "$PW"

    echo "USE $DB; SHOW TABLES;" | mysql  "$HOST" "$USER" "$PW" | (
        while read TABLE; do
            echo $DB.$TABLE
            echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql  "$HOST" "$USER" "$PW" $DB
        done
    )

    PW="pleaseEmptyMeNow"

복사 붙여넣기 bash 스크립트를 사용하는 경우:

var=$(mysql -e 'SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_czech_ci;") AS execTabs FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="zabbix" AND TABLE_TYPE="BASE TABLE"' -uroot -p )

var+='ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci;'

echo $var | cut -d " " -f2- | mysql -uroot -p zabbix

zabbix를 데이터베이스 이름으로 변경합니다.

MySQL 프로시저를 사용하여 답변을 공유하겠습니다.sql 명령어를 3개 실행해야 합니다.

1.

DROP PROCEDURE IF EXISTS UpdateTable;

2.

DELIMITER $$

CREATE PROCEDURE UpdateTable()
BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE _table_name CHAR(255);
    DECLARE cur CURSOR FOR
            SELECT table_name FROM information_schema.tables
            WHERE table_schema = 'my_db_name' AND table_type = "BASE TABLE";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    My_loop: LOOP
        FETCH cur INTO _table_name;
        SET @my_table_name = _table_name;

        IF done THEN
          LEAVE My_loop;
        END IF;

        SET FOREIGN_KEY_CHECKS = 0;

        SET @stmt = CONCAT('ALTER TABLE ', @my_table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'); 
        PREPARE stmt1 FROM @stmt; 
        EXECUTE stmt1; 
        DEALLOCATE PREPARE stmt1;   
        
        SET FOREIGN_KEY_CHECKS = 1;
            
    END LOOP;
    CLOSE cur;

END$$

DELIMITER ;

3.

CALL UpdateTable();

그럼 다시 한 번 실행해봐.프로시저를 저장하지 않을 경우.

언급URL : https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation

반응형
저작자표시

'programing' 카테고리의 다른 글

멀티프로세서 풀과 유사한 스레드 풀?  (0) 2022.10.25
오늘부터 날짜별로 SQL 선택  (0) 2022.10.25
C++의 화살표(->) 연산자의 공식 이름은 무엇입니까?  (0) 2022.10.25
링크가 없는 JavaScript BLOB 파일 이름  (0) 2022.10.25
mySQ에서 커스텀 ORDER BY 오더를 정의하는 방법l  (0) 2022.10.25

'programing'의 다른글

  • 현재글데이터베이스의 모든 테이블을 하나의 대조로 변환하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바