MySQL ERROR 1406 (22001): Data too long for column

Question

Consider the table marks:

mysql> SELECT * FROM marks;
+--------+------------+-----------------------+
| name   | percentage | email                 |
+--------+------------+-----------------------+
| Bipin  | 84%        | [email protected]     |
| Deepak | 78%        | [email protected] |
| Rohith | 82%        | [email protected]    |
| Sujit  | 94%        | [email protected]   |
+--------+------------+-----------------------+

When I made the following query to insert values to the table, it resulted in an error:

mysql> INSERT INTO marks VALUES('&name','&percentage','&email');
ERROR 1406 (22001): Data too long for column 'percentage' at row 1

It was done as an experiment based on info about another way to insert value using ('&') in SQL.

Can you specify what went wrong? According to the blog, simply pressing ENTER will insert values after the query.

The desired output:

Enter value for name: John
Enter value for percentage: 76%
Enter value for email: [email protected]

@GMB, I have zero knowledge of Oracle. Thank you for sharing that info. So just want to know such a feature exists in MySQL. — Bipin
@Biping: as far as I know there is no such thing as a command prompt in MySQL. — GMB
That code is trying to put the literal string &percentage into the table. — Barmar

Barmar Barmar · Accepted Answer · 2020-01-14T00:58:40

In MySQL, session variables are prefixed with @, and they're not expanded inside quotes. So it should be:

mysql> SET @name = 'John', @percentage = '76%', @email = '[email protected]';
mysql> INSERT INTO marks VALUES(@name,@percentage,@email);

There's no automatic prompt for variables like in Oracle, you have to set them by hand.

MySQL ERROR 1406 (22001): Data too long for column

1 Answers