mysql - #1265 - 将列更改为 NOT NULL 时，第 2 行第 '' 列的数据被截断

Question

这是我正在处理的数据库表的结构:

 CREATE TABLE `schools` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 NOT NULL,
`describtion` text CHARACTER SET utf8 NOT NULL,
`keywords` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`status` varchar(255) CHARACTER SET utf8 NOT NULL,
`location` varchar(255) CHARACTER SET utf8 NOT NULL,
`school_type` varchar(255) CHARACTER SET utf8 NOT NULL,
`fees` varchar(255) CHARACTER SET utf8 NOT NULL,
`ownership` varchar(255) CHARACTER SET utf8 NOT NULL,
`thumbnail` varchar(255) NOT NULL,
`logo` varchar(255) NOT NULL,
`founded_at` varchar(255) NOT NULL,
`courses` text CHARACTER SET utf8 NOT NULL,
`undergradcourses` text CHARACTER SET utf8 NOT NULL,
`postgradcourses` text CHARACTER SET utf8 NOT NULL,
`shortcoursesandcertifications` text CHARACTER SET utf8 NOT NULL,
`technicalandvocationalcourses` text CHARACTER SET utf8 NOT NULL,
`website` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`entry_requirement` varchar(255) NOT NULL,
`rating_nigeria` varchar(255) NOT NULL,
`rating_africa` varchar(255) NOT NULL,
`created_at` varchar(255) NOT NULL,
`updated_at` varchar(255) NOT NULL,
`school_name` varchar(50) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`entrance_exam` varchar(50) DEFAULT NULL,
`religious_affiliation` varchar(50) DEFAULT NULL,
`term_structure` varchar(255) DEFAULT NULL,
`school_year` varchar(50) DEFAULT NULL,
`centers_of_excellence` varchar(50) DEFAULT NULL,
`ranking_metro_manila` varchar(50) DEFAULT NULL,
`ranking_philippines` varchar(50) DEFAULT NULL,
`0` varchar(50) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这是我的前两行数据:

  INSERT INTO `schools` (`id`, `name`, `describtion`, `keywords`, `url`, `status`, `location`, `school_type`, `fees`, `ownership`, `thumbnail`, `logo`, `founded_at`, `courses`, `undergradcourses`, `postgradcourses`, `shortcoursesandcertifications`, `technicalandvocationalcourses`, `website`, `phone`, `email`, `entry_requirement`, `rating_nigeria`, `rating_africa`, `created_at`, `updated_at`, `school_name`, `type`, `entrance_exam`, `religious_affiliation`, `term_structure`, `school_year`, `centers_of_excellence`, `ranking_metro_manila`, `ranking_philippines`, `0`) 
  VALUES
  (1, 'Centro Escolar University - Manila', 'Centro Escolar University is a private university in Manila, Philippines. It was founded on June 3, 1907 by two women, Librada Avelino and Carmen de Luna, and was originally called Centro Escolar de Señoritas. It became a university in 1933.', '', 'schools/centro-escolar-university---manila', '', 'Manila', 'Select Type', '10000 - 50000', 'Ownership', '2 - CEU1.PNG', '2 - CEU.jpg', '1907 ', '', 'School of Accountancy and Management\r\n<br>Bachelor of Science in Accountancy\r\n<br>Bachelor of Science in Business Administration Major in Financial Management\r\n<br>Bachelor of Science in Business Administration Major in Management\r\n<br>Bachelor of Science in Business Administration Major in Management Accounting\r\n<br>Bachelor of Science in Business Administration Major in Management with Specialization Track in Service Management in Business Process Outsourcing\r\n<br>Bachelor of Science in Business Administration Major in Marketing Management\r\n<br>Bachelor of Science in Legal Managemen\r\n\r\nSchool of Dentistry\r\nDentistry\r\n\r\nSchool of Education, Liberal Arts, Music and Social Work\r\n<br>Bachelor in Library and Information Science\r\n<br>Bachelor of Arts (Curriculum A) in Mass Communication and Broadcast\r\n<br>Bachelor of Arts (Curriculum A) in Mass Communication and Journalism\r\n<br>Bachelor of Arts (Curriculum A) in Mass Communication and Performing Arts\r\n<br>Bachelor of Arts major in Political Science\r\n<br>Bachelor of Arts with 18 units of Education (Curriculum B) in Mass Communication and Broadcast\r\n<br>Bachelor of Arts with 18 units of Education (Curriculum B) in Mass Communication and Journalism\r\n<br>Bachelor of Arts with 18 units of Education (Curriculum B) in Mass Communication and Performing Arts\r\n<br>Bachelor of Arts with 18 units of Education (Curriculum B) major in Political Science\r\n<br>Bachelor of Elementary Education Major in Pre-school Education\r\n<br>Bachelor of Elementary Education Major in Special Education\r\n<br>Bachelor of Music\r\n<br>Bachelor of Music (Music Education)\r\n<br>Bachelor of Science in Social Work\r\n<br>Bachelor of Secondary Education major in Biological Science\r\n<br>Bachelor of Secondary Education major in English\r\n<br>Bachelor of Secondary Education major in Mathematics\r\n-<br>Bachelor of Secondary Education major in Physical Science\r\n\r\nSchool of Medical Technology\r\n<br>Bachelor of Science in Medical Technology\r\n\r\nSchool of Nursing\r\n<br>Bachelor of Science in Nursing\r\n\r\nSchool of Nutrition and Hospitality Management\r\n<br>Bachelor of Science in Hotel and Restaurant Management\r\n<br>Bachelor of Science in Nutrition and Dietetics\r\n<br>Bachelor of Science in Tourism Management\r\n\r\nchool of Pharmacy\r\n<br>Bachelor of Science in Pharmacy\r\n<br>Bachelor of Science in Pharmacy specialized in Clinical Pharmac\r\n\r\nSchool of Science and Technology\r\n<br>Bachelor of Science in Biology\r\n<br>Bachelor of Science in Computer Engineering\r\n<br>Bachelor of Science in Computer Scienc\r\n<br>Bachelor of Science in Cosmetic Science\r\n<br>Bachelor of Science in Information Technology\r\n<br>Bachelor of Science in Psychology', 'School of Accountancy and Management\r\n<br>Master of Science in Accountancy\r\n<br>Master of Science in Business Administration Major in Financial Management\r\n<br>Master of Science in Business Administration Major in Management\r\n<br>Master of Science in Business Administration Major in Management Accounting\r\n<br>Master of Science in Business Administration Major in Management with Specialization Track in Service Management in Business Process Outsourcing\r\n<br>Master of Science in Business Administration Major in Marketing Management\r\n<br>Master of Science in Legal Managemen\r\n\r\nSchool of Dentistry\r\nDentistry\r\n\r\nSchool of Education, Liberal Arts, Music and Social Work\r\n<br>Master in Library and Information Science\r\n<br>Master of Arts (Curriculum A) in Mass Communication and Broadcast\r\n<br>Master of Arts (Curriculum A) in Mass Communication and Journalism\r\n<br>Master of Arts (Curriculum A) in Mass Communication and Performing Arts\r\n<br>Master of Arts major in Political Science\r\n<br>Master of Arts with 18 units of Education (Curriculum B) in Mass Communication and Broadcast\r\n<br>Master of Arts with 18 units of Education (Curriculum B) in Mass Communication and Journalism\r\n<br>Master of Arts with 18 units of Education (Curriculum B) in Mass Communication and Performing Arts\r\n<br>Master of Arts with 18 units of Education (Curriculum B) major in Political Science\r\n<br>Master of Elementary Education Major in Pre-school Education\r\n<br>Master of Elementary Education Major in Special Education\r\n<br>Master of Music\r\n<br>Master of Music (Music Education)\r\n<br>Master of Science in Social Work\r\n<br>Master of Secondary Education major in Biological Science\r\n<br>Master of Secondary Education major in English\r\n<br>Master of Secondary Education major in Mathematics\r\n-<br>Master of Secondary Education major in Physical Science\r\n\r\nSchool of Medical Technology\r\n<br>Master of Science in Medical Technology\r\n\r\nSchool of Nursing\r\n<br>Master of Science in Nursing\r\n\r\nSchool of Nutrition and Hospitality Management\r\n<br>Master of Science in Hotel and Restaurant Management\r\n<br>Master of Science in Nutrition and Dietetics\r\n<br>Master of Science in Tourism Management\r\n\r\nchool of Pharmacy\r\n<br>Master of Science in Pharmacy\r\n<br>Master of Science in Pharmacy specialized in Clinical Pharmac\r\n\r\nSchool of Science and Technology\r\n<br>Master of Science in Biology\r\n<br>Master of Science in Computer Engineering\r\n<br>Master of Science in Computer Scienc\r\n<br>Master of Science in Cosmetic Science\r\n<br>Master of Science in Information Technology\r\n<br>Master of Science in Psychology', 'Short Courses & Certifications\r\n-Associate in Accounting Technology Management\r\n-Associate in Hotel and Restaurant Management\r\n-Associate in Library and Information Science\r\n-Elementary Teacher Certificate\r\n-Secondary Teacher Certificate', '', 'http://www.ceu.edu.ph/', '(02) 735-6861 to 71', 'ceuadmission@ceu.edu.ph', '', '', '', '', '2018-02-11 16:11:58', 'Centro Escolar University', ' Private University', ' Required', ' Non-sectarian', ' Semester', ' starts in June', 'Teacher Education', '20th ', '83 ', NULL),
  (2, 'Central Institute of Technology', '<span style="color:lightgrey;"><i>This has not been updated yet</i></span>', '', 'schools/central-institute-of-technology', '', 'Manila', '', '10000 - 50000', '', '', 'central-institute-of-technology.png', '', '', 'Medical and Allied<br>-<br>Bachelor of Science in Radiologic Technology<br><br>Education Science and Teacher Training<br>-<br>Bachelor of Secondary Education Major in History<br><br>Architectural and Town-Planning<br>-<br>Bachelor of Science in Architecture', 'Medical and Allied<br>-<br>Master of Science in Radiologic Technology<br><br>Education Science and Teacher Training<br>-<br>Master of Secondary Education Major in History<br><br>Architectural and Town-Planning<br>-<br>Master of Science in Architecture', '', '', 'http://www.ccp.edu.ph/', '(+632) 715 5170 loc. 134', 'admission@ccp.edu.ph', '', '', '', '', '2018-02-02 09:16:49', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

尝试将“type”和“term_struct”列更改为 NOT NULL，但出现错误(如问题中所示)。

有什么想法吗？

Answer 1

使用这篇文章自己修复了它:MySQL alter table modify column failing at rows with null values

用随机文本替换所有 NULL 实例。然后我用空字符串替换了随机文本。然后我将 NULL 更改为 NOT NULL。