Change the collation of a sql view-CodePudding

I currently have a view I have created in SQL, It looks at another database, and the view is then used in another process. The problem is that the 2 different databases have a collation conflict, one is CI_AS and the other CP1_CI_AS.

I've looked over the web and seem to find solutions to change the collation on the joins when I link the view to another database, but I can't do that as its an internal process within the product, all it allows me to do is give it a view.

Is there a way I can change the views collation?

CodePudding user response：

Your question is a little bit not clear, but maybe you could try something like this:

SET @tmp_character_set_client = (select VARIABLE_VALUE 
                                 from performance_schema.session_variables
                                 where VARIABLE_NAME = 'character_set_client');

SET @tmp_character_set_results = (select VARIABLE_VALUE 
                                  from performance_schema.session_variables
                                  where VARIABLE_NAME = 'character_set_results');

SET @tmp_collation_connection = (select VARIABLE_VALUE 
                                 from performance_schema.session_variables
                                 where VARIABLE_NAME = 'collation_connection');

SET SESSION character_set_client = 'utf8';
SET SESSION character_set_results = 'utf8';
SET SESSION collation_connection = 'utf8_general_ci';

CREATE VIEW view_name
AS
select ...;

SET SESSION character_set_client = @tmp_character_set_client;
SET SESSION character_set_results = @tmp_character_set_results;
SET SESSION collation_connection = @tmp_collation_connection;

If you have MySQL8 try to use utf8mb4 and utf8mb4_0900_ai_ci instead of utf8 and utf8_general_ci. If it still doesn't work experiment with different collations

CodePudding user response：

You must apply the COLLATE operator to each column in the SELECT clause.

As an exemple...

With the view :

CREATE VIEW V_CUST
AS
SELECT CustID, CustName, CustCity, CustDate
FROM   Customer
GO

You need to re-create the view as :

CREATE VIEW V_CUST
AS
SELECT CustID, CustName COLLATE French_BIN, CustCity  COLLATE French_BIN, CustDate
FROM   Customer
GO