If I use the command “SET NAMES utf8” as part of my default PDO connection (so that a connection to the database is opened and immediately “SET NAMES utf8” is run), and I have a table or column that is not set to utf8, will using the “SET NAMES utf8” command forcefully put data in encoded as utf8 and so bypass the table or columns default character set? I have been recommended to add SET NAMES to a database adapter class in order to standardize connections to the DB, but I am concerned that I may override table or column defaults, but I also want to ensure that wrongly encoded data is not entered into the database.
SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]
SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES ‘cp1251’ tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)
This leaves it ambiguous whether SET NAMES will override a column’s default character set or collation. I am not finding a clear way of understanding how SET NAMES would influence an simple INSERT statement when a database/table/column has a default character set or collation.
I’m still struggling to find an answer to this question. I don’t have a diverse enough dataset to test it on either, with a column/collation/charset combination that would clearly not be compatible with another column/collation/charset combination. The docs don’t specify clearly, google isn’t helpful. Does anyone have an idea on how I might find an answer to this?
Here is some code for clarity:
SET NAMES utf8;
then
CREATE TABLE `set_names_test` (
`test_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`latin1_only` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`ujis_only` varchar(20) CHARACTER SET ujis DEFAULT NULL,
`table_with_default_charset` varchar(20) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Now if I try to INSERT something into column latin1_only, which is set to character set latin1, and without specifying a character set in my query itself, will the value of my INSERT be encoded in latin1 or in utf8?
I guess no one has any idea what the answer to my question might be, but if someone could suggest a way I might figure it out (without the relevant dataset multilingual dataset, which I lack, unless that can be provided), that would be step in the right direction for me.