3 ms·

Except it's MySQL, so by the time you read it out it says: id first last birth_year 1 Blaise Pascal 1623 2

by rspeer 10y ago

Except it's MySQL, so by the time you read it out it says: id first last birth_year 1 Blaise Pascal 1623 2 Gottfried Leibniz 1646 3 Emmy Noether 1882 4 Muhammad al-KhwÄ�rizmÄ« 780 5 Alan Turing 1912 6 Srinivasa Ramanujan 1887 7 Ada Lovelace 1815 8 Henri PoincarÃ© 1854 (I know, I know. It is possible to configure MySQL encodings correctly. And given that they've put a lot of engineering thought into choosing MySQL, they certainly have.)

codedokode 10y ago
I used both Postgres and MySQL and in my opinion encodings are more difficult to setup with Postgres. You need to change some "template" when creating a database to use unicode. Otherwise it will use latin1. I do not even understand what a database "template" is, how it is related to encodings, and why it is so overcomplicated. In MySQL there is no templates and you can change the encoding of a table at any time using ALTER TABLE statement. And in cloud IDEs like c9.io you cannot use unicode collations in Postgres because they need to be installed separately and they are not installed.
- rosser 10y ago
  So ... you'd rather have a database with mixed encodings, on a table-by-table basis? Ugh.
- pritambaral 10y ago
  You certainly don't need to change any templates when creating a database, CREATE DATABASE and createdb both support specifying the encoding. Changing encoding of a table (or a database after creation) is usually not a wise thing to do; since the db engine is not going to go over all the data and convert it anyway, so if your data is corrupted now it will remain corrupted. If one simply needs to tell the DB to treat the data differently without worrying about data conversion, the encoding of a database is stored in `pg_database` and can be changed with an UPDATE query. And this is all from the official documentation. I'm not a Postgres expert.
- wtbob 10y ago
  I can confirm. I recently set up a private mattermost server. Because the host I'm running it on didn't have the UTF-8 locale on by default (I think), Postgres defaulted to Latin-1, and mattermost ran fine, right up until someone sent a UTf-8 character which is unrepresentable in Latin-1. That was No Fun™ to solve.
tracker1 10y ago
Don't know if it's still an issue, but I once setup mysql, with an id of binary(16) to store UUIDs, and used 0-padded conversions for legacy id's... when trying to port over existing records I discovered the index on a binary field was a "case-insensitive" textual index by default, so started getting conflicts when the ascii equivalent of "a" and "A" as a byte was inserted, etc. This was about 15 or so years ago, but the irksome nature still sticks with me to this day.
- gkop 10y ago
  I wouldn't be surprised if that behavior remains the same. MySQL seems to be fine with long-standing bugs and other sub-optimal behavior hanging around, eg. https://bugs.mysql.com/bug.php?id=20786 https://bugs.mysql.com/bug.php?id=20786 .

by rspeer 10y ago

codedokode 10y ago

I used both Postgres and MySQL and in my opinion encodings are more difficult to setup with Postgres. You need to change some "template" when creating a database to use unicode. Otherwise it will use latin1. I do not even understand what a database "template" is, how it is related to encodings, and why it is so overcomplicated. In MySQL there is no templates and you can change the encoding of a table at any time using ALTER TABLE statement. And in cloud IDEs like c9.io you cannot use unicode collations in Postgres because they need to be installed separately and they are not installed.

rosser 10y ago
So ... you'd rather have a database with mixed encodings, on a table-by-table basis? Ugh.
pritambaral 10y ago
You certainly don't need to change any templates when creating a database, CREATE DATABASE and createdb both support specifying the encoding. Changing encoding of a table (or a database after creation) is usually not a wise thing to do; since the db engine is not going to go over all the data and convert it anyway, so if your data is corrupted now it will remain corrupted. If one simply needs to tell the DB to treat the data differently without worrying about data conversion, the encoding of a database is stored in `pg_database` and can be changed with an UPDATE query. And this is all from the official documentation. I'm not a Postgres expert.
wtbob 10y ago
I can confirm. I recently set up a private mattermost server. Because the host I'm running it on didn't have the UTF-8 locale on by default (I think), Postgres defaulted to Latin-1, and mattermost ran fine, right up until someone sent a UTf-8 character which is unrepresentable in Latin-1. That was No Fun™ to solve.

tracker1 10y ago

Don't know if it's still an issue, but I once setup mysql, with an id of binary(16) to store UUIDs, and used 0-padded conversions for legacy id's... when trying to port over existing records I discovered the index on a binary field was a "case-insensitive" textual index by default, so started getting conflicts when the ascii equivalent of "a" and "A" as a byte was inserted, etc. This was about 15 or so years ago, but the irksome nature still sticks with me to this day.

gkop 10y ago
I wouldn't be surprised if that behavior remains the same. MySQL seems to be fine with long-standing bugs and other sub-optimal behavior hanging around, eg. https://bugs.mysql.com/bug.php?id=20786 https://bugs.mysql.com/bug.php?id=20786 .