Database Collation

Austin · 06-23-2021 08:18 AM

Anyone that uses databases, have you noticed anything around what collation you use in your databases? Trying to be a better DBA for our team and make sure everything is lined up and formatted as best as we could need it and I noticed that we have mixed and matched collations everywhere but I haven’t ever really noticed any oddities that could be caused by that and I figure appsheet converts the data to their character set when they read it anyway. We have purely english data and the most I could ever see our company adding would be Spanish since we’re a southern east US company.

Marc_Dillon

I can’t add much here, but this is what I’ve seen and have used.

The default collation and charset in Google Cloud MySQL is

utf8_general_ci 	utf8

If you need to store emojis (and possibly other special characters?) in your database, you need to use:

utf8mb4_unicode_ci 	utf8mb4

Although Appsheet itself is going to strip out any emojis from your data before sending it to a database, to avoid issues with non-compatible collation/charset. (emojis in the data can be used by Appsheet, just can’t add new ones via Appsheet).

Given the above, I assume Appsheet may have other safeguards in place to groom any entered data in order to avoid potential errors with mis-matched settings.

Austin

I knew they could be used but had no idea you couldn’t store them from appsheet
The tool we use to build our databases defaults to latin1_swedish_ci but our main tool for data analysis and manipulation defaults it’s outputs in utf8_general_ci so I think I’ll go with that. Thanks!