I have a table with two fields:
id int NOT NULL AUTO_INCREMENT,
nombre varchar(20) NOT NULL DEFAULT ''
and a script with the following insert:
INSERT INTO articulos VALUES (21,"Accesorio Derivación");
The script is in a text file with UTF8 encoding.
My problem occurs when from the MySQL console I try to run the script with:
source archivo.sql;
It gives me the error "ERROR 1406 (22001): Data too long for column 'name' at row 1".
What it does is replace the "ó" with "├│" and there remains a longer character, 21 long, that's why it gives the error.
I have the base and the table defined with:
DEFAULT CHARACTER SET utf8mb4
If I execute the same insert from a QueryTab of the Workbench, it works correctly taking the characters.
How can I get the characters correctly from the script and fix this problem??? Even adding some function on the string in the insert like CONVERT would help me.
To avoid this, simply put this at the beginning of your script or file:
SET NAMES 'utf8';
, that way the console charset will be set toutf8
.The script should look like this:
Note that
SET NAMES 'utf8';
it's considered a separate query, and if you're working from APIs like thatmysqli
you should use multiquery.If you are interested in always having
utf8
, you can do it from the configuration file, usually called.mysql.cnf
, by adding the following entry:That way the console will always have the
utf8
character set, check the MR about it .It is recommended to have a configuration file, where, in addition to the charset, you indicate sensitive data such as username, password, etc, so that you are not obliged to write the password in the console (the servers keep logs of everything that is written in the console ). With the configuration file you would connect in this way more or less:
Assuming that in
.unaCarpetaOcultaYSegura
you have a file named.mysql.cnf
with your credentials, charset settings and so on. That folder would have administrator permissions and when you connect you would never be typing your password in the console. For more details on this consult the MR .