MySQL
MySQL databases for HelpSpot version 4 should default to the InnoDB storage engine and to a UTF-8mb4 character set and collation.
For MySQL 5.5.3+, we can use the character set utf8mb4, which is a more complete implementation of UTF-8.
Note that you should change the database name (helpspot_db) as needed for your use case. You may also need to assign a specific user to the database.
PostgreSQL
To create a UTF-8 database in PostgreSQL, you can run a similar query:
Note that you should change the database name and owner as needed for your use case.
SqlServer
SqlServer uses UTF16 by default and likely needs no further instructions on using a UTF-8 friendly database when creating a new database for use with HelpSpot. Users of the Windows Installer will find the new database is created for you, and so most users will not need to worry about creating a new database.
The conversion process will be required to convert existing database data, which may not be saved in UTF-8 encoding.
For the following MySQL create database statement, what would be the equivalent in postgresql?:
I currently have:
Is that enough or should I be more specific including LOCALE as well?
1 Answer 1
Yes, you can be more specific.
Also I recommend to read the following pages about locales and collations in PostgreSQL:
I’ve googled a lot about this problem. To sum up, this is what my my.ini looks like:
When I get into mysql via cmd and issue: show variables like «%character%»;show variables like «%collation%»; , this is what I got:
I’ve already restart the MySQL service, so could anyone give me some idea on how to change character_set_database as well as collation_database to utf8 ? Thanks a lot.
3 Answers 3
This actually isn’t a setting in the my.cnf (or my.ini in this case). mySQL gets this setting from the database’s own collation (when it was created). Inorder to get this inline with the utf8 encoding you want, do this:
then do a restart on mysql (cant remember if its needed though), followed by a:
All should be well, Hope that helps!
side note: i think default-character-set is deprecated now-a-days (mySQL 5.5+) and seems to make the config file fidgety.
determine which charset/collations are available
check charset
set charset (in configure file -> my.cnf)
check database/table charset
change the database/table charset：
set when create database/table:
Note： I heard that in Mysql utf8 is not the true utf8，utf8mb4 is the real utf8. so, if you have special character that can’t save to mysql, maybe you should use utf8mb4 and utf8mb4_general_ci
How to configure Character Set and Collation .
For applications that store data using the default MySQL character set and collation ( latin1, latin1_swedish_ci ), no special configuration should be needed. If applications require data storage using a different character set or collation, you can configure character set information several ways:
- Specify character settings per database. For example, applications that use one database might require utf8 , whereas applications that use another database might require sjis.
- Specify character settings at server startup. This causes the server to use the given settings for all applications that do not make other arrangements.
- Specify character settings at configuration time, if you build MySQL from source. This causes the server to use the given settings for all applications, without having to specify them at server startup.
The examples shown here for your question to set utf8 character set , here also set collation for more helpful( utf8_general_ci collation`).
Specify character settings per database
Specify character settings at server startup
Specify character settings at MySQL configuration time