Tag Archives: MySQL Error 1406

Error 1406 (22001) in MySQL: data too long for column (Fixed)

In mysql, ERROR 1406 (22001):data too long for column
: DEFAULT CHARSET=utf8 is added at the end of the created table


drop table if exists sys_user;
create table sys_user (
  user_id           bigint(20)      not null auto_increment    comment 'User ID',
  dept_id           bigint(20)      default null comment       'Department ID',
  login_name        varchar(30)      not null comment         'login account',
  user_name       varchar(30)       not null comment       'user nickname',
  user_type       varchar(2)       default '00' comment       'user type (00 system user)',
  email       varchar(50)       default '' comment       'user email',
  phonenumber varchar(11)       default '' comment       'phone number',
  sex       char(1)       default '0' comment       'User gender (0 male 1 female 2 unknown)',
  avatar       varchar(100)       default '' comment       'avatar path',
  password       varchar(50)       default '' comment       'password',
  salt       varchar(20)       default '' comment       'salt encryption',
  status       char(1)       default '0' comment       'Account status (0 normal 1 disabled)',
  del_flag       char(1) default '0' comment 'Delete flag (0 means present 2 means deleted)',
  login_ip       varchar(50)       default '' comment 'last login ip',   login_date datetime comment 'last login',
  create_by       varchar(64)       default '' comment 'creator',   create_time datetime comment 'create time',
  update_by       varchar(64)       default '' comment 'updater',  update_time datetime comment 'update time',
  comment       varchar(500)       default null comment 'Remarks', primary key (user_id)
) engine=innodb auto_increment=100 comment = 'user information table' DEFAULT CHARSET=utf8;

insert into sys_user values(1,  103, 'admin', 'admin', '00', '[email protected]', '15888888888', '1', '', '123456', '111111', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'ADMIN');
insert into sys_user values(2,  105, 'ry',    'admin', '00', '[email protected]',  '15666666666', '1', '', '123456', '222222', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'TEST');