union all 한글 검색 관련 질문입니다.
본문
해결이 난해한 오류가 있어서 글을 올립니다.
옐르들어
g5_write_table1
g5_write_table2 가 있습니다.
여기서 뷰테이블이 각테이블 하나씩 바라보게 하여 view_table1,view_table2 를 만들었습니다.
SELECT wr_id,wr_subject FROM view_table1 WHERE wr_subject LIKE '%한글%' ;
SELECT wr_id,wr_subject FROM view_table2 WHERE wr_subject LIKE '%한글%' ;
이렇게 했을시 이상없이 잘 처리가 됩니다. 다만, 뷰 테이블을 g5_write_table1,g5_write_table2를 합쳐서 view_table로 임의로 생성했을시, 한글 검색이 안되는 문제가 생겼습니다.
CREATE
VIEW `view_table`
AS select
`g5_write_table1`.`wr_id` AS `wr_id`,
`g5_write_table1`.`wr_subject` AS `wr_subject`
from `g5_write_table1`
union all
select
`g5_write_table2`.`wr_id` AS `wr_id`,
`g5_write_table2`.`wr_subject` AS `wr_subject`
from `g5_write_table2`
그리하여
SELECT wr_id,wr_subject FROM view_table WHERE wr_subject LIKE '%한글%' ;
쿼리를 실행하면, error도 없이 아무것도 안나오네요.(영어 및 숫자는 잘 처리됩니다.)
오류를 굳이 나오게 하니,
Warning: #1300 Cannot convert string '%\xEC\x98\x88\xEC\x95...' from utf8mb4 to binary
이렇게 나옵니다.
db역시 utf8mb4로 생성되어있고, 각 테이블 캐릭터셋 및 필드도 utf8mb4, 콜레이션 utf8mb4_general_ci로 설정되어있습니다.[mysql8]
+----------------------------------------------+----------------------------+
| Variable_name | Value |
+----------------------------------------------+----------------------------+
| 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 | utf8mb4 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------------------------------+----------------------------+
제가 놓치고 있는게 있는지 혹은 이 증상 겪어본이 계시거나 비슷한 경험이 있는지요?
답변 6
검색되는 변수 그부분에서 한글이 깨져요
근데 create view는 뭔가요 셀렉트에서만 가능할건데
뷰 테이블을 생성하여, 거기에서 select를 했습니다.
그렇군요 고수분들 답변 보고 생각해보세요~ 저는 유니온만 해봐서 알지만... 다른건 잘...
view를 생성할때 인코딩지정을 해보셔야 할것같습니다.
아무래도 view를 생성하면서
dbms의 메모리에 할당되는 컬럼의 인코딩은
database 기본값을 따를것인데
미지정시 무엇이 되는지 알수없다는것이죠
OS를 따를지 DBMS를 따를지 기본값이 무엇인지 등등의 변수가 존재할수있으니
CREATE
VIEW `view_table`
AS select
`g5_write_table1`.`wr_id` AS `wr_id`,
`g5_write_table1`.`wr_subject` AS `wr_subject`
from `g5_write_table1` COLLATE utf8mb4
union all
select
`g5_write_table2`.`wr_id` AS `wr_id`,
`g5_write_table2`.`wr_subject` AS `wr_subject`
from `g5_write_table2` COLLATE utf8mb4;
와 같이 강제 지정을 해보시고
그래도 한글이 깨지면 utf8mb4_general_ci 로변경해서 한번더해보시는것도 추천합니다.
참고
https://stackoverflow.com/questions/46353062/mysql-5-6-create-view-with-unicode-character-set
답변 감사합니다. 우선 실패를 했습니다.
어떻게 해야할지 고민을 더 해야겠네요.
dbms 가 설치된 OS의 character-set을 한번 확인해보세요
view라는것이 아무래도
dbms 상에서 메모리에 상주시켜서 조회를 하게 되는것을
접근하는것일테니
OS의 character set 이 latin1 이라던지 utf8 로만되어있으면 역시 다르게 작동할것같거든요
utf8과 utf8mb4 자체가 바이트수가 달라서 인식이 다를수있을겁니다.
이후 관련자료를 검색하니
참고할만한 블로그들이 있네요
https://cirius.tistory.com/1769
https://blog.lael.be/post/917
show create table 하셔서
wr_subject의 character set도 확인해 보세요.
답변 감사드립니다.
view테이블 및 실제 테이블도 모두 확인한 결과 이상이 없네요.
정 안되면, 유사하게 백업서버에 같이 구축하고 해결을 하는 수밖에 없을듯 합니다.
Select * from (
SELECT wr_id,wr_subject FROM view_table1
Union
LECT wr_id,wr_subject FROM view_table2 )
Vtbl WHERE wr_subject LIKE '%한글%'
이렇게 해도 에러가 나나요?
네 실제 테이블 및 뷰 테이블에서 union 을 쓰면 검색이 안됩니다.
호~옥~시
lib/common.lib.php
function sql_query($sql, $error=G5_DISPLAY_SQL_ERROR, $link=null)
{
global $g5, $g5_debug;
if(!$link)
$link = $g5['connect_db'];
// Blind SQL Injection 취약점 해결
$sql = trim($sql);
// union의 사용을 허락하지 않습니다.
이 부분 확인해 보셨나요?
... WHERE wr_subject LIKE binary '%한글%'
이렇게 한번 해보세요
해보았는데 오류가 나더라고요.
몇몇 설정을 다시 해보고 바꿔도 증상이같고, 결국 손도 대지 못하게 mysql character_set_system 마저 utf8mb3로 바꼈습니다.
os locale : en_US.UTF-8 ubuntu 22.04 입니다.
information_schema 데이터 정렬방식 utf8mb3_general_ci
mysql 데이터 정렬방식 utf8mb4_0900_ai_ci
performance_schema 데이터 정렬방식 utf8mb4_0900_ai_ci
mysql> SHOW VARIABLES LIKE "char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
추가적으로 pad설정입니다.
mysql> select * from information_schema.collations where COLLATION_NAME like 'utf8mb4_0900%' or COLLATION_NAME = 'utf8mb4_bin' or COLLATION_NAME like 'utf8mb4_general%';
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
utf8mb4_0900_ai_ci 의 IS_DEFAULT의 Yes가 찜찜하긴 합니다만. 저건 아니겠지요.
캐릭터셋 시스템은 mysql에서 테이블 참고하는 거라 상관이 없을듯한데, 너무 산으로 간게 아닌가 싶네요. 결국 해결은 못했습니다. 소중한 시간내어 답변주셔서 감사드립니다.
show create table g5_write_table2
이거 실행한 결과 좀 볼 수 있을까요?
테이블 이름은 샘플이고, 원하시는게 이 부분같아 답변 남겨드립니다.
g5_write_table2 | CREATE TABLE `g5_write_table2` (
`wr_id` int NOT NULL AUTO_INCREMENT,
`wr_num` int NOT NULL DEFAULT '0',
`wr_reply` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
`wr_parent` int NOT NULL DEFAULT '0',
`wr_is_comment` tinyint NOT NULL DEFAULT '0',
`wr_comment` int NOT NULL DEFAULT '0',
`wr_comment_reply` varchar(5) COLLATE utf8mb4_general_ci NOT NULL,
`ca_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_option` set('html1','html2','secret','mail') COLLATE utf8mb4_general_ci NOT NULL,
`wr_subject` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`wr_content` longtext COLLATE utf8mb4_general_ci NOT NULL,
`wr_seo_title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`wr_link1` longtext COLLATE utf8mb4_general_ci NOT NULL,
`wr_link2` longtext COLLATE utf8mb4_general_ci NOT NULL,
`wr_link1_hit` int NOT NULL DEFAULT '0',
`wr_link2_hit` int NOT NULL DEFAULT '0',
`wr_hit` int NOT NULL DEFAULT '0',
`wr_good` int NOT NULL DEFAULT '0',
`wr_nogood` int NOT NULL DEFAULT '0',
`mb_id` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`wr_password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_email` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_homepage` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_datetime` datetime NOT NULL,
`wr_file` tinyint NOT NULL DEFAULT '0',
`wr_last` varchar(19) COLLATE utf8mb4_general_ci NOT NULL,
`wr_ip` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_facebook_user` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_twitter_user` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_1` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_2` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_3` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_4` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_5` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_6` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_7` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_8` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_9` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
`wr_10` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`wr_id`),
KEY `wr_seo_title` (`wr_seo_title`),
KEY `wr_num_reply_parent` (`wr_num`,`wr_reply`,`wr_parent`),
KEY `wr_is_comment` (`wr_is_comment`,`wr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
신경써주셔서 감사드립니다.
마지막으로 query 실행전에
set names utf8mb4
한번 실행해 보세요.
mysqli_query($dbconn, "set names utf8mb4");
위와 같은식으로 했어도, 제대로 작동 안됐습니다. 무엇인가 잘못된 것 같네요. 각각은 잘되는데, union만 쓰면 저러니 도통 이해를 못하겠습니다. 답변 감사합니다. 새롭게 비슷한 환경을 구축해서 해봐야겠어요. 감사합니다.
