Re: Default collation used in STRCMP() function
Posted by: sachin vyas
Date: August 26, 2012 11:09PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Default collation used in STRCMP() function
2760
August 26, 2012 11:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.