create database ateama CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


create user `ateama`@`localhost` identified by '12341234';  
grant all privileges on ateama.* to `ateama`@`localhost` ;


create user `ateama`@`%` identified by '12341234';  
grant all privileges on ateama.* to `ateama`@`%` ;

 

슈퍼 권한

GRANT SUPER ON *.* TO user1@localhost

 

MYSQL 이모티콘 설정 utf8 로 하면 깨진다. 따라서,

 charset만 utf8이 아닌 utf8mb4로 적용해주면 간단히 처리가 가능

 

 MySQL 한글 설정

• my.ini 파일

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server=utf8mb4

2. 한글 설정 확인

show variables like 'c%';

MySQL 재시작

 

 

> show variables like 'c%';
+----------------------------------+------------------------------------+
| Variable_name                    | Value                              |
+----------------------------------+------------------------------------+
| character_set_client             | euckr                              |
| character_set_connection         | euckr                              |
| character_set_database           | utf8mb4                            |
| character_set_filesystem         | binary                             |
| character_set_results            | euckr                              |
| character_set_server             | utf8mb4                            |
| character_set_system             | utf8                               |
| character_sets_dir               | C:\bitnami\mariadb\share\charsets\ |
| check_constraint_checks          | ON                                 |
| collation_connection             | euckr_korean_ci                    |
| collation_database               | utf8mb4_unicode_ci                 |
| collation_server                 | utf8mb4_unicode_ci                 |
| column_compression_threshold     | 100                                |
| column_compression_zlib_level    | 6                                  |
| column_compression_zlib_strategy | DEFAULT_STRATEGY                   |
| column_compression_zlib_wrap     | OFF                                |
| completion_type                  | NO_CHAIN                           |
| concurrent_insert                | AUTO                               |
| connect_timeout                  | 10                                 |
| core_file                        | ON                                 |
+----------------------------------+------------------------------------+

 

 

 

 

기존 테이블 모두 utf8mb4_unicode_ci 변경 처리 방법

  ALTER TABLE tbl_board
  MODIFY boardType VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '게시판유형',
  MODIFY boardSubType VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'all' COMMENT '게시판 서브타입',
  MODIFY isSecret VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '게시판제목',
  MODIFY contentsId VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '컨텐츠제목',
  MODIFY discloseContent VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '컨텐츠 공개 on',
  MODIFY place VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  MODIFY content MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  MODIFY oriCode MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  MODIFY address VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  MODIFY boardPassword VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '게시판비밀번호',
  MODIFY ip VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '등록및업데이트아이피',
  MODIFY fileName VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '이미지명',
  MODIFY latitude VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '위도',
  MODIFY longitude VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '경도',
  MODIFY tags VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '태그',
  MODIFY url VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;



  ALTER TABLE tbl_bannergroup COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_attach COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_banner COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_faq COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_note COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_point COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_pointexchange COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_reply COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_user COLLATE=utf8mb4_unicode_ci;
  ALTER TABLE tbl_visitor COLLATE=utf8mb4_unicode_ci;

 

                    
                      
                      

SET @database_name = 'cryptocurrencyCommunity'; -- 데이터베이스 이름 설정

-- 모든 테이블의 collation 변경을 위한 변수 초기화
SET @sql = NULL;

SELECT
    GROUP_CONCAT(CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') SEPARATOR ' ')
INTO @sql
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = @database_name
    AND TABLE_TYPE = 'BASE TABLE'; -- 기본 테이블만 선택

-- 생성된 SQL 실행
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

                      
                      
                      
                      
SET @database_name = 'cryptocurrencyCommunity'; -- 데이터베이스 이름 설정

-- 모든 테이블과 컬럼의 collation 변경을 위한 변수 초기화
SET @sql = NULL;

SELECT
    GROUP_CONCAT(CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') SEPARATOR ' ')
INTO @sql
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = @database_name
    AND COLLATION_NAME != 'utf8mb4_unicode_ci'; -- 현재 collation이 다른 경우만 선택

-- 생성된 SQL 실행
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
                   

 

 

 

                      
 SELECT 
    CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA = '데이터베이스명';
                     

 

 

변경되었는지 확인

        SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLLATION_NAME 
FROM 
    information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = '데이터베이스명';