select x, y, z, '검색' as h from ~ ?

핫.. 그런데 이상한 점이 있습니다..T^T

select x,y,z, '검색' as h from a ~ union

select x,y,z, '하하하' as h from b ~

b 테이블에는 h필드에 '하하하'가 안들어가고 '하하' 이렇게 두글자만 들어 가네요..

위에서 '검색'이라는 거에 영향을 미치는거 같은데요.. 이런 경우는 어떻게 해야 하나요?

mysql 사이트에 있는 메뉴얼을 보니 아래와 같은 설명이 있습니다.

4.1.1 이후 버전을 사용해야한다는 얘기네요.

http://dev.mysql.com/doc/mysql/en/UNION.html

The types and lengths of the columns in the result set of a UNION take into account the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT were used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a |

| b |

+---------------+

That limitation has been removed as of MySQL 4.1.1:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a |

| bbbbbbbbbb |

+---------------+

답변 감사합니다. 그런데..

repeat 같은 경우는 같은 문자를 반복해서 넣는거 같군요..

select x,y,z, '$a' as h from a ~ union

select x,y,z, '$b' as h from b ~

어떻게 방법이 없겠습니까?

비슷한 문제로 해결책을 찾어보다가 이 게시물을 보고 메뉴얼을 보던중 mysql 매뉴얼 코멘트에 유용한 내용이 있어서 옮겨놓습니다.

Queries such as

SELECT c1,NULL AS c2,c3 FROM t1 UNION c1,c2,NULL AS c3 FROM t2 ORDER BY c1;

can be made to work as expected (in MySQL versions from 4.0.3 to 4.1.1) by using an always true or always false IF function in the appropriate column of the first select.

SELECT c1,IF(1,NULL,c2) AS c2,c3 FROM t1 UNION c1,c2,NULL AS c3 FROM t2 ORDER BY c1;

The IF function will use the type of the non-NULL value where a NULL value is encounted.

It apparently also works for expanding the length of static string values. That is:

SELECT 'a value' AS c UNION SELECT 'another value' AS c;

can be made to work as expected by modifying it thus:

SELECT IF(1,'a value',REPEAT(' ',20)) AS c UNION SELECT 'another value' AS c;