CODEWITHSUNDEEP
mysqlsqlinsertsql-insert
Bipin
Bipin

Reputation: 483

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

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]

Upvotes: 4

Views: 4883

Answers (1)

Barmar
Barmar

Reputation: 781004

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.

Upvotes: 6

Related Questions