Home>

Even if you register a word from PHP in MySQL with FreeBSD 11.1, the value will be NULL for some reason. The cause is

+ -------------------------- + ---------------- ------------------ +
| Variable_name | Value |
+ -------------------------- + ---------------------- ------------ +
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir |/usr/local/share/mysql/charsets/|
+ -------------------------- + ---------------------- ------------ +


I thought that the character code of MySQL is not out of UTF8. So I tried changing various settings in /etc/my.cnf to change the MySQL character code, but the character code doesn't change at all. I also tried restarting Mysql. Please answer if you know how to change the character code.

Content of my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL.Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
default-character-set = utf8
sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
Supplement

MySQL5.6

  • Answer # 1

    If you are installing from ports on FreeBSD 11.1

      

    /etc/my.cnf

    instead of

      

    /usr/local/etc/mysql/my.cnf

    It may be

    .
    The character code definition is

    [mysqld]
    ... Abbreviation ...
    character-set-server = utf8


    I think that it will be a description of.
    Also, if the character code has already been determined for a database that has already been created, I think it is better to start over from the creation of the database.

  • Answer # 2

    mysql --help | grep my.cnf

  • Answer # 3

    Why should I specify collate when creating a DB or TABLE?

    create table tbl (
    id int primary key,
    val varchar (30))
    engine = InnoDB charset = utf8mb4 collate utf8mb4_unicode_ci;