Functions

Functions WebDev/*nix assorted functions & hacks

MySQL fetch_field(), fetch_row(), fetch_rows(), mres() query($sql) Unicode Unicode configuration You forgot your MySQL root password AGAIN 5 found

auth

Name:
Category: MySQL
Body:

Disclaimer: hazy on all the different types of utf8 here, but it'll do the right things in 99%.

Even in 2018 it's possible to find installs of MySQL which default to ISO-Latin1 or something for new databases/tables, or utf8_general_ci instead of utf8_unicode_ci.

SHOW variables WHERE variable_name LIKE '%coll%' OR variable_name LIKE '%char%';
  SHOW CREATE TABLE `tableName`;

To fix a field:
  ALTER TABLE `tableName` MODIFY `colName` varchar(12) CHARACTER SET utf8
# To change the default character set of a table:
  ALTER TABLE `tableName` CHARACTER SET utf8
# To change the default character set of a table and all its columns:
  ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8
# To fix the database
  ALTER DATABASE `databasename` CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Stackoverflow has great answers on how utf8_general_ci differs from utf8_unicode_ci; long answer short the *_unicode_* one is more precise and slower, but with today's hardware speed there's no reason to not be precise.

To fix further, edit the config files as root. Find out what the config files /are/ with
  $ mysql --verbose --help | grep -C1 my.cnf
  (good luck finding where mysql actually sits, on this OS X it was /usr/local/mysql/bin)
Here, that yielded
  /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
Prepend that with an 'ls -la' to find which of those exist. Edit/create any of 'm, and put, FOR EXAMPLE,

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

in it. Restart MySQL.
Profit?

Footer

~~Functions~~ WebDev/nix assorted functions & hacks Search* find all Array - Bash - Classes - Config - DateTime - Debug - dotjs - File - foooo - Git - HTML - Images - Javascript - Linux - Math - Misc - MySQL - OS X - Reading - Redis - Shell scripts - snippets - SQL - Strings - Tampermonkey - TextMate - Web - \(^o^)/ Fun! - _Misc hacks - _Pages
MySQL fetch_field(), fetch_row(), fetch_rows(), mres() query($sql) Unicode Unicode configuration You forgot your MySQL root password AGAIN 5 found auth	Name: Category: MySQL Body: Disclaimer: hazy on all the different types of utf8 here, but it'll do the right things in 99%. Even in 2018 it's possible to find installs of MySQL which default to ISO-Latin1 or something for new databases/tables, or utf8_general_ci instead of utf8_unicode_ci. SHOW variables WHERE variable_name LIKE '%coll%' OR variable_name LIKE '%char%'; SHOW CREATE TABLE `tableName`; To fix a field: ALTER TABLE `tableName` MODIFY `colName` varchar(12) CHARACTER SET utf8 # To change the default character set of a table: ALTER TABLE `tableName` CHARACTER SET utf8 # To change the default character set of a table and all its columns: ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 # To fix the database ALTER DATABASE `databasename` CHARACTER SET utf8 COLLATE utf8_unicode_ci; Stackoverflow has great answers on how utf8_general_ci differs from utf8_unicode_ci; long answer short the _unicode_ one is more precise and slower, but with today's hardware speed there's no reason to not be precise. To fix further, edit the config files as root. Find out what the config files /are/ with $ mysql --verbose --help \| grep -C1 my.cnf (good luck finding where mysql actually sits, on this OS X it was /usr/local/mysql/bin) Here, that yielded /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf Prepend that with an 'ls -la' to find which of those exist. Edit/create any of 'm, and put, FOR EXAMPLE, [mysqld] character-set-server=utf8 collation-server=utf8_unicode_ci in it. Restart MySQL. Profit?
Footer