MySQL创建用户表 - 解决常见错误及优化方案

{/n/'title/': /'CREATE TABLE IF NOT EXISTS //userinfo//(//n//tid //t//tINT UNSIGNED UNIQUE AUTO_INCREMENT NOT NULL //t//tcomment ///'用户ID///'/,//n//troles //t//tENUM(///'user///',///'admin///') NOT NULL //t//t//t//t//tcomment ///'角色///'/,//t//t//t//t//t//n//tusername //tVARCHAR(50) UNIQUE NOT NULL //t//t//t//t//tcomment ///'用户名///'/,//t//t//t//t//t//n//tpassword //tVARCHAR(50) NOT NULL//t//t//t//t//t//t//tcomment ///'密码///'/,//t//t//t//t//t//t//t//n//temail //t//tVARCHAR(100) UNIQUE NOT NULL //t//t//t//t//tcomment ///'邮箱///'/,//t//t//t//t//t//n//tvcode //t//tVARCHAR(10) NOT NULL //t//t//t//t//t//t//tcomment ///'邮箱验证码///'/,//t//t//t//t//t//t//t//n//tsex //t//tBOOLEAN DEFAULT 0 //t//t//t//t//t//t//t//tcomment ///'性别///'/,//t//t//t//t//t//t//t//t//n//tage //t//tTINYINT UNSIGNED //t//t//t//t//t//t//tcomment ///'年龄///'/,//t//t//t//t//t//t//t//n//ttitle //t//tVARCHAR(500) //t//t//t//t//t//t//t//tcomment ///'简介///'/,//t//t//t//t//t//t//t//n//tavatar //t//tVARCHAR(100) //t//t//t//t//t//t//tcomment ///'头像///'/,//t//t//t//t//t//t//n//tregister //tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'注册时间///'/,//t//t//t//t//t//n//tonline //t//tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'上线时间///'/,//t//t//t//t//t//n//tdownline //tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'下线时间///'/,//t//t//t//t//t//n//tulike //t//tINT UNSIGNED //t//t//t//t//t//t//t//tcomment ///'喜欢///'/,//t//t//t//t//t//t//t//n//tucollect //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'收藏///'/,//t//t//t//t//t//t//n//tuhistory //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'历史记录///'/,//t//t//t//t//t//t//n//tip //t//tVARCHAR(15) //t//t//t//t//t//t//tcomment ///'IP地址///'/,//t//t//t//t//t//t//n//tis_delete //tBOOLEAN DEFAULT 0//t//t//t//t//t//tcomment ///'是否注销，默认否///'/,//t//t//t//t//t//n//tfeedback //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'反馈///'/,//t//t//t//t//t//t//n//tsystem_msg //tINT UNSIGNED //t//t//t//t//t//tcomment ///'系统消息///'/,//t//t//t//t//t//n//tis_talk //tBOOLEAN DEFAULT 0 //t//t//t//t//t//tcomment ///'是否禁言///'/,//t//t//t//t//t//n//twarning //tTINYINT UNSIGNED //t//t//t//t//t//tcomment ///'警告次数///'/,//t//t//t//t//t//n//tucomment //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'我的评论///'/,//t//t//t//t//t//t//n//tarticle //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'我的文章///'/,//t//t//t//t//t//t//n//tPRIMARY KEY(id,username)//,//n//tINDEX idx_username (username)//,//n//tINDEX idx_email (email)//,//n//tINDEX idx_register (register)//n)ENGINE=INNODB DEFAULT CHARSET=UTF8;/n/nmysql为什么还是报错内容：根据提供的SQL语句，我们可以看到在创建表//userinfo//时，列//ip//的数据类型定义为//VARCHAR(15) UNSIGNED//，这是不正确的。//VARCHAR//类型不能与//UNSIGNED//一起使用。如果要存储IP地址，可以使用//VARCHAR(15)//或者//INT UNSIGNED//类型。/n/n正确的SQL语句如下：/n/n/nCREATE TABLE IF NOT EXISTS //`userinfo//`(//n//tid //t//tINT UNSIGNED UNIQUE AUTO_INCREMENT NOT NULL //t//tcomment ///'用户ID///'/,//n//troles //t//tENUM(///'user///',///'admin///') NOT NULL //t//t//t//t//tcomment ///'角色///'/,//t//t//t//t//t//n//tusername //tVARCHAR(50) UNIQUE NOT NULL //t//t//t//t//tcomment ///'用户名///'/,//t//t//t//t//t//n//tpassword //tVARCHAR(50) NOT NULL//t//t//t//t//t//t//tcomment ///'密码///'/,//t//t//t//t//t//t//t//n//temail //t//tVARCHAR(100) UNIQUE NOT NULL //t//t//t//t//tcomment ///'邮箱///'/,//t//t//t//t//t//n//tvcode //t//tVARCHAR(10) NOT NULL //t//t//t//t//t//t//tcomment ///'邮箱验证码///'/,//t//t//t//t//t//t//t//n//tsex //t//tBOOLEAN DEFAULT 0 //t//t//t//t//t//t//t//tcomment ///'性别///'/,//t//t//t//t//t//t//t//t//n//tage //t//tTINYINT UNSIGNED //t//t//t//t//t//t//tcomment ///'年龄///'/,//t//t//t//t//t//t//t//n//ttitle //t//tVARCHAR(500) //t//t//t//t//t//t//t//tcomment ///'简介///'/,//t//t//t//t//t//t//t//n//tavatar //t//tVARCHAR(100) //t//t//t//t//t//t//tcomment ///'头像///'/,//t//t//t//t//t//t//n//tregister //tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'注册时间///'/,//t//t//t//t//t//n//tonline //t//tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'上线时间///'/,//t//t//t//t//t//n//tdownline //tTIMESTAMP NOT NULL //t//t//t//t//tcomment ///'下线时间///'/,//t//t//t//t//t//n//tulike //t//tINT UNSIGNED //t//t//t//t//t//t//t//tcomment ///'喜欢///'/,//t//t//t//t//t//t//t//n//tucollect //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'收藏///'/,//t//t//t//t//t//t//n//tuhistory //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'历史记录///'/,//t//t//t//t//t//t//n//tip //t//tVARCHAR(15) //t//t//t//t//t//t//tcomment ///'IP地址///'/,//t//t//t//t//t//t//n//tis_delete //tBOOLEAN DEFAULT 0//t//t//t//t//t//tcomment ///'是否注销，默认否///'/,//t//t//t//t//t//n//tfeedback //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'反馈///'/,//t//t//t//t//t//t//n//tsystem_msg //tINT UNSIGNED //t//t//t//t//t//tcomment ///'系统消息///'/,//t//t//t//t//t//n//tis_talk //tBOOLEAN DEFAULT 0 //t//t//t//t//t//tcomment ///'是否禁言///'/,//t//t//t//t//t//n//twarning //tTINYINT UNSIGNED //t//t//t//t//t//tcomment ///'警告次数///'/,//t//t//t//t//t//n//tucomment //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'我的评论///'/,//t//t//t//t//t//t//n//tarticle //tINT UNSIGNED //t//t//t//t//t//t//tcomment ///'我的文章///'/,//t//t//t//t//t//t//n//tPRIMARY KEY(id,username)//,//n//tINDEX idx_username (username)//,//n//tINDEX idx_email (email)//,//n//tINDEX idx_register (register)//n)ENGINE=INNODB DEFAULT CHARSET=UTF8;/n/n/n请注意，该SQL语句仅适用于使用UTF8字符集和InnoDB存储引擎的MySQL数据库。如果您使用的是其他字符集或存储引擎，请相应地调整语句。