Hermann Himmelbauer wrote: > Hi, > I need to store multibyte characters into my database (MaxDB). To my mind, > there are two approaches: > > 1) Store everything in UTF-8 format in normal varchars. I don't know if > MaxDB > will have problems with this, probably there will be errors such as that > the > string length can be faulty. > 2) Store everything in the 2-byte Unicode format - but this will nearly > double > the size of my database, moreover I don't know if I will have to convert > every string from/to UTF-8 to/from Unicode. > > What's the appropriate solution?
It is (as always) depending - on the number of characters not being ASCII-7-Bit, but being stored with 3 or more bytes in UTF8. You said, that storing in UCS2 will nearly double, so we can assume, ASCII-7-bit is the main thing and only few character will need more than one byte even in UTF8 - on the functions / qualifications you need. With UTF8 stored in (VAR)CHAR (n) BYTE you will not be able to handle LENGTH, SUBSTR, (x)FILL, (x)TRIM, and so on or LIKE correctly. Sorting could do, perhaps even in a way you can live with. - on the knowledge about the data you have. If you want to store at maximum n character (no matter if ASCII or UNICODE), you will have to specify (VAR)CHAR (n). But if you want to store n UTF8-character in a (VAR)CHAR(x)BYTE-column, which value is needed for x (not to be too small if several character need more than just one byte)? BTW: the database then will not be able to check if more than n character are stored in this value. - (VAR)CHAR(n) BYTE cannot be compared to (VAR)CHAR(n) ASCII/UNICODE, but on the other hand: to store UTF8 in (VAR)CHAR(n) ASCII is not the recommended way of using, although I do not know any knockout-criteria for this. - For storing as UTF8 no _UNICODE=YES-instance is needed, meaning that no unicode-storage of the catalog (info about tables, columns, views, users,...) is done. - If a record needs for example half the space in UTF8 than it does in UCS2, double the number of records can be stored in one page, meaning less pages are needed for the same number of records, meaning less I/O / smaller data cache needed for all records if your search strategies are so bad to need a scan. ==> if you are sure that you now and ever will only need a stupid container for your data and all handling (functions and so on, LIKE) will be done in the application, then maybe (although storage is not too expensive) storing as UTF8 is a good decision. If you really know this... Elke SAP Labs Berlin > > Best Regards, > Hermann > > -- > [EMAIL PROTECTED] > GPG key ID: 299893C7 (on keyservers) > FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
