有个表格,在创建表格的时候已经指定字段不能为空,可是调用储存过程中插入数据的时候字段为空时居然没有报错,也没有抛出异常,是不是要自己去验证数据是否为空或者是定义异常机制.
解决方案 »
- linux 环境,连接MYSQL不上,用127.0.0.1可以,但是用localhost连接不上,是什么原因?
- group by之后,如何将多行串联成一行的。
- 有人能帮忙解读个SQL命令么
- 再搞不定我就死了,关于一个存储过程中的IF语句
- 如何把大文件存到 MySQL 数据库中呢?
- mysql 的默认时间如何写,像ms sql的getdate()
- 在MYSQL官方文档中的关于数据类型长度问题的一点不理解。感觉MYSQL不是很规范
- mysql 用命令createdb 建数据库的问题
- 求救~关于MYSQL配置ODBC数据源中的一个问题
- 关于mysql启动失败的问题
- 有没有可能拒绝某种客户端连接服务器
- 关于user()与system_user()的区别
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set
mysql> 如果没有开启严格模式是不会报错的
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+----------------------------------------------------------------+
现在应该怎么改才会满足要求
orderitemid bigint(20) uisigned not null auto_increment,
pname varchar(32) not null,
price double not null,
pid bigint(20) unsigned not null,
number int(10) unsigend not null,
oid varchar(36) not null,
primary key(orderitemid ),
key pname(pname),
key oid(oid),
key pid(pid)
)ENGINE=InnoDB auto_increment=10 default charset=utf8
-> orderitemid bigint(20) uisigned not null auto_increment,
-> pname varchar(32) not null,
-> price double not null,
-> pid bigint(20) unsigned not null,
-> number int(10) unsigend not null,
-> oid varchar(36) not null,
-> primary key(orderitemid ),
-> key pname(pname),
-> key oid(oid),
-> key pid(pid)
-> )ENGINE=InnoDB auto_increment=10 default charset=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '{
orderitemid bigint(20) uisigned not null auto_increment,
pname varchar(32) not' at line 1
mysql>
楼主就是用这段代码建表的???
建议你原来复制你的 show create table orderitem; 然后什么都不要改。同时把你的insert 语句也可以提供一下。
修改mysql配置文件my.ini里的sql-mode参数,改成STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
'orderitemid' bigint(20) uisigned not null auto_increment,
'pname' varchar(32) not null,
'price' double not null,
'pid' bigint(20) unsigned not null,
'number' int(10) unsigend not null,
'oid' varchar(36) not null,
primary key('orderitemid' ),
key 'pname'('pname'),
key 'oid'('oid'),
key 'pid'('pid')
)ENGINE=InnoDB auto_increment=10 default charset=utf8
没改 上面有个括号打错了,显示就是这样的.有些大写改成小写应该没问题的
mysql> create table 'orderitem'(
-> 'orderitemid' bigint(20) uisigned not null auto_increment,
-> 'pname' varchar(32) not null,
-> 'price' double not null,
-> 'pid' bigint(20) unsigned not null,
-> 'number' int(10) unsigend not null,
-> 'oid' varchar(36) not null,
-> primary key('orderitemid' ),
-> key 'pname'('pname'),
-> key 'oid'('oid'),
-> key 'pid'('pid')
-> )ENGINE=InnoDB auto_increment=10 default charset=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ''orde
ritem'(
'orderitemid' bigint(20) uisigned not null auto_increment,
'pname' ' at line 1
mysql>
'orderitemid' bigint(20) uisigned NOT NULL AUTO_INCREMENT,
'pname' varchar(32) not null,
'price' double not null,
'pid' bigint(20) unsigned not null,
'number' int(10) unsigend not null,
'oid' varchar(36) not null,
PRIMARY KEY('orderitemid' ),
KEY 'pname'('pname'),
KEY 'oid'('oid'),
KEY 'pid'('pid')
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
现在是一模一样了,不知道开头和结尾的|拿来做什么,我创建表的时候用的index被改成key了。
ACMAIN_CHM:
有没有碰过这样的情况,感觉怪怪的.
'orderitemid' bigint(20) uisigned NOT NULL AUTO_INCREMENT,
'pname' varchar(32) not null,
'price' double not null,
'pid' bigint(20) unsigned not null,
'number' int(10) unsigend not null,
'oid' varchar(36) not null,
PRIMARY KEY('orderitemid' ),
KEY 'pname'('pname'),
KEY 'oid'('oid'),
KEY 'pid'('pid')
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
CREATE TABLE 'orderitem'(
'orderitemid' bigint(20) uisigned NOT NULL AUTO_INCREMENT,
'pname' varchar(32) n....
建议你描述一下你是实现情况。mysql> CREATE TABLE `orderitem`(
-> `orderitemid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `pname` varchar(32) not null,
-> `price` double not null,
-> `pid` bigint(20) unsigned not null,
-> `number` int(10) unsigned not null,
-> `oid` varchar(36) not null,
-> PRIMARY KEY(`orderitemid` ),
-> KEY `pname`(`pname`),
-> KEY `oid`(`oid`),
-> KEY `pid`(`pid`)
-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)mysql> insert into orderitem values (null,null,null,null,null,null);
ERROR 1048 (23000): Column 'pname' cannot be null
mysql>