mysql cannot add rows with "UNION" 1

[lupidol]

Hello everyone,
Here is code to create table named 'abcd'/

USE skullcrashingwords;
DROP TABLE IF EXISTS abcd;
CREATE TABLE abcd(
  ID INT AUTO_INCREMENT, 
  Name VARCHAR(255) not null collate utf8_unicode_ci,
KEY (ID)
);

INSERT INTO abcd(name) 
SELECT 'aaaaa';

When I run this code a new abcd table is created containing one row.
When I try to create that table with 2 lines I run the following:

USE skullcrashingwords;
DROP TABLE IF EXISTS abcd;
CREATE TABLE abcd(
  ID INT AUTO_INCREMENT, 
  Name VARCHAR(255) not null collate utf8_unicode_ci,
KEY (ID)
);

INSERT INTO abcd(name) 
SELECT 'aaaaa';

UNION
SELECT 'bbbb';

But here I recieve the following error message:
[pre]ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION
SELECT 'bbbb'' at line 1[/pre]
Can anyone help me with that enigmatic error ?
Thanks

 

[mikrom]

It's one statement not two, so remove the first semicolon and try

INSERT INTO abcd(name) 
SELECT 'aaaaa'
UNION ALL
SELECT 'bbbb'
;

 

[lupidol]

Thanks,
unnecessary semicooln was illcopied, sorry for that.
Even though your code yielded error message it works !
I use "UNION" alone on tables that have INDEXES attached and it works ! Table with "KEY" column and with no accompanied INDEX "UNION" alone doesn't work but your suggestion made it work !
Thanks, but I still wonder why...

 

[Chris Miller]

For union the types of all columns have to match the target columns. And since you specify a collation in the name column, but only use string literals without a specified collation, that might already cause a warning about possible loss of data in conversion. Key columns pose another problem, but that concerns the insert part, not the union of records that finally are inserted.

Chriss

 

[lupidol]

How do I specify collation ?

 

[Chris Miller]

Just look into the documentation:

https://dev.mysql.com/doc/refman/8.0/en/charset-literal.html

So if you write

_utf8'aaaaa' collate utf8_unicode_ci

you make it clear this is the same collation as your name column. Just 'aaaaa' is interpreted as whatever your connection or default collation is.

Chriss