0

I am new to MySQL. When I declare a column as VARCHAR, why does it automatically change to VARBINARY? How should I avoid this?

<code>newly created database</code>

Database changed                                                       
mysql> CREATE TABLE sync(
-> vlan_id INT NOT NULL,
-> vlan_name VARCHAR(90) NOT NULL,
-> vlan_description VARCHAR(100) NOT NULL,
-> PRIMARY KEY(vlan_id)
-> );
Database changed
mysql> SHOW CREATE TABLE sync;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sync  | CREATE TABLE `sync` (
  `vlan_id` int(11) NOT NULL,
  `vlan_name` varbinary(90) NOT NULL,
  `vlan_description` varbinary(100) NOT NULL,
  PRIMARY KEY (`vlan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
1
  • I fixed it running ALTER TABLE sync CONVERT TO CHARACTER SET utf8;. Please let me know if it is the correct solution.
    – unknown29
    Aug 6, 2019 at 17:20

1 Answer 1

0

I'm guessing you have binary as your default character set for the server and/or for the database. You probably want to change this to prevent the same thing happening next time you create a table.

You can check by running: SHOW VARIABLES LIKE 'character_set_server'; and SHOW CREATE DATABASE the_database_name;

If the database doesn't have a character set, then the server character set will be used.

You can change the database character set with a command like:

ALTER DATABASE the_database_name CHARACTER SET = 'utf8';

The server character set can be set with a command like:

SET character_set_server = 'utf8';

(Use SET GLOBAL ... for the setting to be made outside of the current session. Or you can set this in your config file so it survives a restart.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.