[TYPO3-dev] MySQL, search and utf8

[Steffen Kamper]

"Steffen Kamper" <steffen at sk-typo3.de> schrieb im Newsbeitrag 
news:mailman.1.1191880845.21662.typo3-dev at lists.netfielders.de...
>
> "ries van Twisk" <typo3 at rvt.dds.nl> schrieb im Newsbeitrag 
> news:mailman.1001.1191868206.20721.typo3-dev at lists.netfielders.de...
>
> On Oct 8, 2007, at 1:05 PM, Martin Kutschker wrote:
>
>> Steffen Kamper schrieb:
>>> Hi,
>>>
>>> i have a non-TYPO3-question.
>>>
>>> I perform a search in MySQL and for example look for the word  "schön". 
>>> So my
>>> query looks like:
>>>
>>> Select .... where ... and (body like "schön%" or body like "%schön %" or 
>>> body
>>> like "%schön" or body = "schön")
>>>
>>> looking to the results i see that MySQL shows up result with the word
>>> "schon" which is different. Thoug it is utf8, the letter ö is  saved in 
>>> utf8
>>> as 2-Byte, so why gives MySQL me the result with o?
>>
>> Mysql knows three character sets: the one the clients sends (queries),
>> the one the server uses (data storage) and the one the the server  sends
>> (query results).
>>
>> Seems like the last settings is for some reason not UTF8. See the  Mysql
>> docs on how to set (and check) those settings.
>
> It has something todo what collation is used internally for sorting
> and filtering...
> In this case MySQL did a good thing and did stick to standards... see
> below..
>
>>
>> Masi
>
>
> Hey steffen,
>
> this document will explain it to you..
> http://dev.mysql.com/doc/refman/5.0/en/charset-collation-effect.html
>
> and this one how to solve your specific problem..
>
> http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
>
> Ries
>
> indeed it seems to work proper with
>
> SELECT ... FROM tx_drwiki_pages COLLATE latin1_german2_ci ...
>
> So i don't really understand what MySQL internally does because i only see 
> collate as a ORDER BY issue but it seems to be more ...
>
> vg  Steffen
>
>
sry, it doesn't work - i only didn't saw a mysql error.
My complete query is

SELECT *,count(*) anz FROM tx_drwiki_pages
WHERE hidden=0 and deleted=0 and (body like "schön%" or body like "%schön%" 
or body like "%schön" or body = "schön")
GROUP BY keyword
ORDER BY anz desc

vg  Steffen