CODEWITHSUNDEEP
mysql
Vidul Talwar
Vidul Talwar

Reputation: 23

How to handle blank entries in MySQL

I have been struggling with entering blank values in MySQL. For example, the code:

INSERT INTO `tablename` (column1, column2) VALUES ('value1', '');

will not work. I always get the following error:

#1265 - Data truncated for column 'column2' at row 1

However, when I run either of the two statements:

INSERT INTO `tablename` (column1, column2) VALUES ('value1', 'value2');

or

INSERT INTO `tablename` (column1, column2) VALUES ('value1', NULL);

the query works just fine. The default value for column2 has been set as "0", still no luck.

I'm facing this issue with all columns in all tables. My application is such that there will be blank entries (cannot be changed). This feature was working fine earlier, I started getting this error when I shifted the application code to another server.

MySQL server version: 8.0.22 - MySQL Community Server - GPL

Table structure is given below:

enter image description here

I'm facing the error in all columns. Any help would be much appreciated!

Table schema:

CREATE TABLE `recordnew` (
 `id` int NOT NULL AUTO_INCREMENT,
 `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `course_id` int DEFAULT NULL,
 `subject_id` int DEFAULT NULL,
 `teacher_id` int DEFAULT NULL,
 `student_id` int DEFAULT NULL,
 `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `attendance` int DEFAULT NULL,
 `totalclasses` int DEFAULT NULL,
 `tutorialattendance` int DEFAULT NULL,
 `tutorialclassestotal` int DEFAULT NULL,
 `practicalattendance` int DEFAULT NULL,
 `practicalclassestotal` int DEFAULT NULL,
 `testmarks` double(11,2) DEFAULT '0.00',
 `assignmentmarks` double(11,2) DEFAULT '0.00',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68612 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Attendance & Internal Assessment Record'

My actual query:

INSERT INTO `recordnew` 
            (`course_id`, 
             `subject_id`, 
             `teacher_id`, 
             `student_id`, 
             `month`, 
             `attendance`, 
             `totalclasses`, 
             `testmarks`, 
             `assignmentmarks`, 
             `tutorialattendance`, 
             `tutorialclassestotal`, 
             `practicalattendance`, 
             `practicalclassestotal`) 
VALUES      ('13', 
             '17', 
             '10', 
             '1704', 
             'Marks', 
             '0', 
             '0', 
             '15', 
             '', 
             '0', 
             '0', 
             '0', 
             '0')

Error for the above query:

#1265 - Data truncated for column 'assignmentmarks' at row 1

Other SQL query:

INSERT INTO `recordnew` 
            (`course_id`, 
             `subject_id`, 
             `teacher_id`, 
             `student_id`, 
             `month`, 
             `attendance`, 
             `totalclasses`, 
             `testmarks`, 
             `assignmentmarks`, 
             `tutorialattendance`, 
             `tutorialclassestotal`, 
             `practicalattendance`, 
             `practicalclassestotal`) 
VALUES      ('13', 
             '17', 
             '10', 
             '1704', 
             '', 
             '0', 
             '0', 
             '15', 
             '0', 
             '0', 
             '0', 
             '0', 
             '0')

Error for above query:

#1265 - Data truncated for column 'month' at row 1

Upvotes: 0

Views: 1128

Answers (1)

GMB
GMB

Reputation: 222472

Most likely, column2 is of a numeric datatype, not of string-like datatype. The empty string is not a valid numeric value, hence the warning that you are getting.

Guidelines:

  • If you want to store an empty string, then use a string datatype (varchar, char etc).

  • Don't store numbers as strings!

  • If you want to represent the absence of data, use NULL

Upvotes: 1

Related Questions