Firebird - UTF8 Size VarChar

Question

I am changing all varchar columns in our firebird database to UTF8, but I do not understand the difference in varchar size.

For example, if the character set and sorting are set to nothing, we can set the varchar size to 255, if we set the encoding and matching in UTF8, when we set varchar to 255, it reads different values.

What will be the equivalent varchar size for varchar (255) in UTF8?

Answer 1

Using the UTF8 character set for VARCHAR(N) fields, you must reserve enough space for any UTF8 character N The length of one such character can be between 1 and 4, so the only safety is to use N characters of length 4 each, which means that there must be space for 200 bytes for storing 50 characters (in the worst case).

You can use the FlameRobin tool to look at the insides. Suppose you have a table

 CREATE TABLE "TableÅÄÖåäö" ( "ColÅÄÖåäö" Varchar(50) ); 

in the default UTF8 character set database. (Note that you will need at least Firebird 2.0 for this.)

System tables store information about all relationships and their fields. In the system table RDB$RELATION_FIELDS there is an entry for this field that has (for example) RDB$1 as RDB$FIELD_SOURCE . There is one entry for RDB$1 in RDB$FIELD_LENGTH , and its RDB$FIELD_LENGTH value is 200.

So, to answer your question: To have a UTF8 column with a space for 255 characters, enter it as VARCHAR(255) , but it will be 1020 bytes in the database.