Mysql 이모지(이모티콘) 4byte 적용
Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'report_campaign_name' at row 1
### The error may involve com.aereport.mybatis.mapper.ReportCampaignManagementMapper.insertReportCampaign-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO report_campaign_management( report_number, report_campaign_name, start_date, end_date, user_id, advertiser_code, brand_code, custom_brand_name, report_campaign_state, created_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'report_campaign_name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'report_campaign_name' at row 1
# Mysql 설정 변경
> vim /etc/my.ini
---------------------------------------------------------------------
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = utf8mb4
character_set_system = utf8
collation_connection = utf8mb4_unicode_ci
collation_database = utf8mb4_unicode_ci
collation_server = utf8mb4_unicode_ci
---------------------------------------------------------------------
# 스키마 선택
> USE information_schema;
# DataBase 케릭터셋 콜레이션
SELECT concat("ALTER DATABASE `",table_schema,
"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
FROM `TABLES`
WHERE table_schema like "{데이터베이스 이름}"
GROUP BY table_schema;
# DataBase 속한 테이블의 케릭터셋 콜레이션
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
FROM `TABLES`
WHERE table_schema like "{데이터베이스 이름}"
GROUP BY table_schema, table_name;
# 테이블에 속한 컬럼 케릭터셋 콜레이션 변경
ALTER TABLE ${테이블명} MODIFY COLUMN ${컬럼} VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE ads MODIFY COLUMN ad_name VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
# 자동 생성된 샘플
ALTER TABLE `ae_report`.`users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`user_role` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`user_connection` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`role` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`role_permission` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`role_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`report_media_set` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`report_information_detail` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`report_campaign_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`permission` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`permission_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_template_creative` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_keyword_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_keyword_insight` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_creative_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_creative_insight` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_ad_creative_template` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`naver_ad_creative_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`mezzo_team` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`mezzo_employee` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`media_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`media_app_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`files` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`campaign` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`campaign_insight_daily` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`bulletin_board_system` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`brand_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`board_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`authentication_code_entity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`audit_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`api_key_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`advertiser_management` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`ads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`ads_insight_daily` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`adgroup_insight_daily` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `ae_report`.`ad_group` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
'DataBase > mysql' 카테고리의 다른 글
mysql data backup (dump) (0) | 2022.05.11 |
---|---|
mysql collate 변경 column , table (0) | 2020.11.30 |
cent os 7 mysql 8 설치 (0) | 2020.09.15 |
mysql 사용자 추가 DB 권한부여 (0) | 2018.09.18 |
JAVA Mysql 타입 매칭 (0) | 2018.08.06 |