phpmysqlduplicatesmysql-error-1062

MYSQL BUG: Duplicate entry on Primary/Unique. If I insert word , Word


Hi, I try to INSERT in my database 2 different words with PHP.

word <= *is the first*

Word <= *is the secound

Create Code:

CREATE TABLE `myWords_table` (
    `myWords` VARCHAR(250) NOT NULL,
    PRIMARY KEY (`myWords`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

SQL:

INSERT INTO myWords_table (myWords) VALUES ("word"),("Word")

ALSO to better show:

 INSERT INTO myWords_table (myWords) VALUES ("word"),("WORD")

Error: Duplicate entry 'word' for key 'PRIMARY' 

*I also tried Unique Key. It seems like MYSQL can not handle capital vs Small

Can you please help me?*

Status UPDATE

The ERROR is not the space, it is Capital and small letters.


Solution

  • Thanks Jimmy for this answer and everybody who helps me.

    MYSQL bad MYSQL Workbench.

    RUN THIS SQL:

    alter table myWords_table change column myWords myWords varchar(250) CHARSET utf8 COLLATE utf8_bin;
    

    ANY Ideas how to prevent for future against this?

    Jimmys answer: You can set the database's collation to utf8_bin to start with, and then any created table use the default collation will inherit that collation

    Thank you all for this great work. You helped me so much!!

    Greeces,

    Sebstian