character set was different. It is possible to convert the column to use the proper character set but an additional step is required. Suppose that the server’s default character set was latin1  and col1  is defined as CHAR(50)  but its contents are sjis  values. The first step is to convert the column to a binary data type, which removes the existing character set information without performing any character conversion: 

ALTER TABLE t MODIFY col1 BLOB; 

The next step is to convert the column to a nonbinary data type with the proper character set: 

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET sjis; 

This procedure requires that the table not have been modified already with statements such as INSERT or UPDATE  after an upgrade to MySQL 4.1 or higher. In that case, MySQL would store new values in the column using latin1 , and the column would contain a mix of sjis  and latin1  values and cannot be converted properly. 

If you specified attributes when creating a column initially, you should also specify them when altering the table with ALTER TABLE . For example, if you specified NOT NULL  and an explicit DEFAULT  value, you should also provide them in the ALTER TABLE  statement. Otherwise, the resulting column definition does not include those attributes. 

To convert all character columns in a table, the ALTER TABLE ... CONVERT TO CHARACTER SET charset  statement may be useful. See Section 13.1.9, “ALTER TABLE Statement” . 

## 10.8 Collation Issues 

## 10.8.1 Using COLLATE in SQL Statements 

The following sections discuss various aspects of character set collations. 

With the COLLATE  clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples: 

•With ORDER BY : 

SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci; 

•With AS : 

SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1; 

•With GROUP BY : 

SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci; 

•With aggregate functions: 

SELECT MAX(k COLLATE latin1_german2_ci) FROM t1; 

•With DISTINCT : 

SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1; 

