6 ms·

In MySQL, never use “utf8”. Use “utf8mb4”

45 points by suresh70 7y ago

donalhunt 7y ago
previous discussion: https://news.ycombinator.com/item?id=17310690 https://news.ycombinator.com/item?id=17310690 additional context (which the OP didn't reference): https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... https://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ https://mysqlserverteam.com/sushi-beer-an-introduction-of-ut... https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8... hint: "Exactly the same set of characters is available in utf8mb3 and ucs2."
mikl 7y ago
MySQL is a great example how extreme commitment to backwards compatibility makes your whole product backwards. It is chock full of bugs and footguns that you can work around and mitigate if you know how to configure it correctly, but most of those improvements are still not the default. And the `utf8` encoding, that does not fully support UTF-8 looks like it’ll stay around forever. ಠ_ಠ As the author of the post says, much better to just use PostgreSQL, will save you a lot of headaches down the road.
- Deukhoofd 7y ago
  > And the `utf8` encoding, that does not fully support UTF-8 looks like it’ll stay around forever. ಠ_ಠ No, it looks like the exact opposite. As the MySQL documentation clearly states: > The utf8mb3 character set is deprecated and will be removed in a future MySQL release. Please use utf8mb4 instead. Although utf8 is currently an alias for utf8mb3, at some point utf8 will become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
  - orf 7y ago
    I think every developer has written the famous words: “at some point we will remove this”. The accuracy of that statement often seems to be pretty dubious.
    - beatgammit 7y ago
      Exactly. I don't believe something unless there's a time schedule, and even then I'm doubtful if it's further than "the next release or two".
    - evanelias 7y ago
      Doesn't seem dubious to me. I would expect it to be removed (or at least have the utf8 alias changed to point to utf8mb4) in the next major release. utf8mb3 was deprecated in the most recent release series (MySQL 8). For feature deprecation notices, MySQL consistently follows a pattern of deprecate in major release N --> remove/change in major release N+1.
- evanelias 7y ago
  MySQL's defaults have been massively improved in the past two major releases. Can you please cite any major examples of modern-day "bugs and footguns" configuration in MySQL 8 related to bad defaults or backwards compat?
  - dfsegoat 7y ago
    You are correct they addressed many things in recent releases. But for someone who was on MySQL 5.4-5.6 and "learned SQL" on that system - you become comfortable with things that just don't fly in SQL standard: - Aggregate operations allowed without group by - Group bys allowed regardless of selected columns / order by - Case insensitive by default ... those are just a few we've had to deal with. It's on us of course. But when you build your entire legacy app on crap SQL that MySQL taught you - then MySQL 5.7 - 8 becomes irrelevant - you can't upgrade to it without disabling strict mode, or totally refactoring your code etc.
    - evanelias 7y ago
      > crap SQL that MySQL taught you This seems like an unfair characterization. You're blaming MySQL for "teaching you" nonstandard practices just because it allowed you to use them, but where did you actually learn these practices originally? Although strict mode only became the default 4 years ago, it's been available as an option for over 15 years, and is mentioned in numerous places in the MySQL manual -- including a dedicated page on the strict-mode implications on GROUP BY and aggregation. Anyway, I don't see how the need to rewrite some poorly-written legacy queries makes modern versions of MySQL "irrelevant". Seems ironic given the topic of GP complaining about MySQL's extreme commitment to backwards compatibility. If you don't like MySQL for personal historical reasons, that's fine, but this hardly seems like a solid argument for discouraging others from using it.
      - tabtab 7y ago
        Indeed. Things change. Backward compatibility is difficult, and doing it with the minimum possible headaches involves many trade-offs. A lot of things will look "stupid" in hindsight.
- tmm84 7y ago
  I am always left with a weird sense of "what?" when I run into something MySQL does that I wasn't expecting. I find MariaDB to do even more weird optimizations that have led to some long debug sessions to find out what it was doing that caused the problem. I agree with you on PostgreSQL. I think the whole reason most don't use it is because their provider doesn't support/install it by default or the "legacy" system was designed with MySQL because it was already installed.
pwinnski 7y ago
"Never" seems an odd choice. Until somewhat recently the recommendation would have been the now-deprecated "utf8mb3" for which "utf8" is a synonym. Until the next release, when "utf8" is updated to be a synonym for "utf8mb4", yes, use "utf8mb4" directly. At the next release, you can use "utf8" again.
deleted 7y ago
[deleted]

45 points by suresh70 7y ago

previous discussion: https://news.ycombinator.com/item?id=17310690 https://news.ycombinator.com/item?id=17310690 additional context (which the OP didn't reference): https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... https://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ https://mysqlserverteam.com/sushi-beer-an-introduction-of-ut... https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8... hint: "Exactly the same set of characters is available in utf8mb3 and ucs2."

mikl 7y ago

MySQL is a great example how extreme commitment to backwards compatibility makes your whole product backwards. It is chock full of bugs and footguns that you can work around and mitigate if you know how to configure it correctly, but most of those improvements are still not the default. And the `utf8` encoding, that does not fully support UTF-8 looks like it’ll stay around forever. ಠ_ಠ As the author of the post says, much better to just use PostgreSQL, will save you a lot of headaches down the road.

Deukhoofd 7y ago
> And the `utf8` encoding, that does not fully support UTF-8 looks like it’ll stay around forever. ಠ_ಠ No, it looks like the exact opposite. As the MySQL documentation clearly states: > The utf8mb3 character set is deprecated and will be removed in a future MySQL release. Please use utf8mb4 instead. Although utf8 is currently an alias for utf8mb3, at some point utf8 will become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
- orf 7y ago
  I think every developer has written the famous words: “at some point we will remove this”. The accuracy of that statement often seems to be pretty dubious.
  - beatgammit 7y ago
    Exactly. I don't believe something unless there's a time schedule, and even then I'm doubtful if it's further than "the next release or two".
  - evanelias 7y ago
    Doesn't seem dubious to me. I would expect it to be removed (or at least have the utf8 alias changed to point to utf8mb4) in the next major release. utf8mb3 was deprecated in the most recent release series (MySQL 8). For feature deprecation notices, MySQL consistently follows a pattern of deprecate in major release N --> remove/change in major release N+1.
evanelias 7y ago
MySQL's defaults have been massively improved in the past two major releases. Can you please cite any major examples of modern-day "bugs and footguns" configuration in MySQL 8 related to bad defaults or backwards compat?
- dfsegoat 7y ago
  You are correct they addressed many things in recent releases. But for someone who was on MySQL 5.4-5.6 and "learned SQL" on that system - you become comfortable with things that just don't fly in SQL standard: - Aggregate operations allowed without group by - Group bys allowed regardless of selected columns / order by - Case insensitive by default ... those are just a few we've had to deal with. It's on us of course. But when you build your entire legacy app on crap SQL that MySQL taught you - then MySQL 5.7 - 8 becomes irrelevant - you can't upgrade to it without disabling strict mode, or totally refactoring your code etc.
  - evanelias 7y ago
    > crap SQL that MySQL taught you This seems like an unfair characterization. You're blaming MySQL for "teaching you" nonstandard practices just because it allowed you to use them, but where did you actually learn these practices originally? Although strict mode only became the default 4 years ago, it's been available as an option for over 15 years, and is mentioned in numerous places in the MySQL manual -- including a dedicated page on the strict-mode implications on GROUP BY and aggregation. Anyway, I don't see how the need to rewrite some poorly-written legacy queries makes modern versions of MySQL "irrelevant". Seems ironic given the topic of GP complaining about MySQL's extreme commitment to backwards compatibility. If you don't like MySQL for personal historical reasons, that's fine, but this hardly seems like a solid argument for discouraging others from using it.
    - tabtab 7y ago
      Indeed. Things change. Backward compatibility is difficult, and doing it with the minimum possible headaches involves many trade-offs. A lot of things will look "stupid" in hindsight.
tmm84 7y ago
I am always left with a weird sense of "what?" when I run into something MySQL does that I wasn't expecting. I find MariaDB to do even more weird optimizations that have led to some long debug sessions to find out what it was doing that caused the problem. I agree with you on PostgreSQL. I think the whole reason most don't use it is because their provider doesn't support/install it by default or the "legacy" system was designed with MySQL because it was already installed.

pwinnski 7y ago

"Never" seems an odd choice. Until somewhat recently the recommendation would have been the now-deprecated "utf8mb3" for which "utf8" is a synonym. Until the next release, when "utf8" is updated to be a synonym for "utf8mb4", yes, use "utf8mb4" directly. At the next release, you can use "utf8" again.

deleted 7y ago

[deleted]