MySQL and Oracle case-sensitive issues

The case of MySQL is not a big problem, but if you do not understand it, it will confuse the user;

SQL code
INSERT into t values (' A ');

INSERT into t values (' a ');

When the second execution, if it is a primary key or a unique constraint, it will occur

SQL code
Duplicate entry for XXX

More confusing is the following 2 statements

Java code
INSERT into t values (' ~ ');

INSERT into t values (' Y ');

Insert errors can also occur.

This problem also occurs when you query.

Java code
/* Query Results one eye */
SELECT * FROM t where a like ' a% '
SELECT * FROM t where a like ' A% '

/* The following query results are the same */
SELECT * FROM t where a = ' Y ';
SELECT * FROM t where a = ' ~;

1. Why the problem arises

MySQL under Linux database name, table name, column name, alias casing rules are like this:

1, database name and table name are strictly case-sensitive;
2, the table alias is strictly case-sensitive;
3, the column name and the alias of the column in all cases are ignored case;
4, variable names are also strictly case-sensitive;

MySQL is case insensitive under Windows.

MySQL is case insensitive when querying strings, Yi MySQL is generally used to ISO-8859 character set as the default character set, this character set is not sensitive to case, so in the comparison process of Chinese-encoded character case conversion caused this phenomenon.


2. Workaround

A. Table names are case-sensitive

Join in my.conf:
Ower_case_table_names = 0
Where 0: Case sensitive, 1: Case insensitive

B. Case-sensitive when inserting queries

field values need to be set to the binary property to be case sensitive.

There are several ways to set up:
Settings at creation time:
CREATE TABLE T (
A VARCHAR (Ten) BINARY
);

Use ALTER to modify:
ALTER TABLE ' tablename ' MODIFY COLUMN ' cloname ' VARCHAR BINARY;

The binary item is checked directly in the MySQL table editor.

There are two cases in Oracle, where simple SQL statements are not case-sensitive, but if you query a character, you need to be case sensitive.

1, if the following circumstances, is not case-sensitive, the query results are consistent:

select* from emp;SELECT * FROMEMP;

2, such as in the EMP table query ename "SMITH" (without quotation marks) information, you must pay attention to the case:

select* from emp whereename=‘SMITH‘;

This is a result,

select* from emp whereename=‘smith‘;

This will not result in the query:

MySQL and Oracle case-sensitive issues