Rick James,
Thanks for the information.
I referred the links, but :
1) Probably I am still not able to know the value for the default collation used in my case.
2) I need some clarification w.r.t. a comment in the documentation in the link:
http://dev.mysql.com/doc/refman/5.5/en/charset-collation-expressions.html
For 1)
========
My scenario is :
I created a database using the following command:
CREATE DATABASE test_db
DEFAULT CHARACTER SET utf8;
I created a table "testts3" & columns & did not specify any character set or collation, so it is inherited from the DB.
The default collation for the character set utf8 is utf8_general_ci.
The table structure is :
CREATE TABLE `testts3` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varchar(5) DEFAULT NULL,
`ts1` timestamp NULL DEFAULT NULL,
`ts2` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
I added some data to above table. I want to update ts2 if old.data <> new.data (in trigger).
My OLD trigger code was:
CREATE TRIGGER testts3Trig BEFORE UPDATE ON testts3 FOR EACH ROW
BEGIN
IF STRCMP(old.data, new.data) <> 0 THEN
SET new.ts2 = CURRENT_TIMESTAMP();
END IF;
END;
The value for old.data was 'D1' & that for new.data was 'd1'.
But, the ts2 column was not getting updated, I further verified that the result for STRCMP() was coming as zero.
>>> What was the collation used in this case i.e. from which variable connection_collation, server_collation, database_collation etc ? Is there a way to determine this using some function etc?
I then modified the above code and added COLLATE in STRCMP() function & got the expected result.
....
IF STRCMP(old.data, new.data COLLATE utf8_bin) <> 0 THEN
....
2) For 2)
===========
As per the link :
http://dev.mysql.com/doc/refman/5.5/en/charset-collation-expressions.html
MySQL uses coercibility values with the following rules to resolve ambiguities:
Use the collation with the lowest coercibility value.
If both sides have the same coercibility, then:
>>> If both sides are Unicode, or both sides are not Unicode, it is an error.
In my case, in STRCMP(), both sides are UNICODE, but I AM NOT GETTING AN ERROR.
Regards,
Sachin Vyas.