mysql 脚本变量和 max function-2 [英] mysql script variables and max function-2

问题描述

我在这里问了一个关于如何获取 sql 函数的问题(max) 结果为@variable 以及如何使用它.我得到的答案非常简单和有用(回复回复者:Tom Mac).不，我在我的程序中使用了相同的机制，但我得到了一个不可接受的结果，我将在这里解释:请看看我的 mysql 控制台，一切都是不言自明的.甚至我的问题:) :

<代码>>mysql>从命令中选择 max(command_idx) 到 @max_command_idx 中；>查询正常，1 行受影响(0.00 秒)

现在第一次使用变量:

mysql>插入命令(controller_idx,subcontroller_idx,command_idx,controller_id, subcontroller_id,code,status,type,plan_name,timetable_id,offset,ctime,user_name,result) 值(0,0,@max_command_idx+1,'937','SUB0','SMS',0,'CONFIG','NA','NA',0,NOW(),'admin',0);查询正常，1 行受影响(0.00 秒)

现在将变量第二次用于另一个表:

<块引用>

<代码>>mysql>插入>csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,电话)>值(0,0,@max_command_idx+1,0,'[phone]');错误 1048 (23000):>列command_idx"不能为空

.........................................................

我的问题:你能告诉我为什么我会收到这个错误吗?

谢谢…………………………………………………………………………………………………………………………………………………………………….....................

关于我的桌子的更多信息:

mysql>desc csmslist;+--------------------+----------------+------+-----+---------+-------+|领域 |类型 |空 |钥匙 |默认 |额外 |+--------------------+----------------+------+-----+---------+-------+|控制器_idx |整数(11) |否 |PRI |空 |||子控制器_idx |整数(11) |否 |PRI |空 |||command_idx |整数(11) |否 |PRI |空 |||csmslist_idx |整数(11) |否 |PRI |空 |||电话 |varchar(24) |是 ||空 ||+--------------------+----------------+------+-----+---------+-------+5 行(0.01 秒)mysql>desc 命令；+--------------------+-------------+------+-----+-------------------+------------------------------+|领域 |类型 |空 |钥匙 |默认 |额外 |+--------------------+----------------+------+-----+-------------------+------------------------------+|控制器_idx |整数(11) |否 |PRI |空 |||子控制器_idx |整数(11) |否 |PRI |空 |||command_idx |整数(11) |否 |PRI |空 |自动增量||controller_id |varchar(24) |是 ||空 |||子控制器_id |varchar(24) |是 ||空 |||代码 |varchar(12) |是 ||空 |||状态 |整数(11) |是 ||空 |||类型 |varchar(24) |是 ||空 |||计划名称 |varchar(24) |是 ||空 |||timetable_id |varchar(24) |是 ||空 |||偏移|整数(11) |是 ||空 |||时间 |时间戳 |否 ||CURRENT_TIMESTAMP |更新 CURRENT_TIMESTAMP ||用户名 |varchar(80) |是 ||空 |||结果|整数(11) |是 ||空 ||+--------------------+----------------+------+-----+-------------------+------------------------------+14 行(0.01 秒)

解决方案

命令表有一个 auto_increment，当你将列设置为 null(这是你正在做的，不管你认为你在做什么)，它只是对其进行自动增量.

csmslist 表没有自动递增，因此插入空值失败.

改为这样做:

插入csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,phone)values(0,0,LAST_INSERT_ID(),0,'[phone]');错误 1048 (23000):列command_idx"不能为空

在插入到 command 时，只需将列设为 NULL，让数据库自动为您做事.

可能是命令表是空的，所以它最大返回NULL.

顺便说一句，您可以将查询放在那里，而不是使用变量:

插入csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,phone)values(0,0,(select max(command_idx) from command),0,'[phone]');ERROR 1048 (23000):列command_idx"不能为空

I had asked a question here on how to get a sql function(max) result into a @variable and how to use it. The answer I got was very straightforward and useful(tanx to the responder: Tom Mac). No I am using the same mechanism in my program and I get an unacceptable result that I'll explain here: Please Have a look at my mysql console and everything is self explanatory. Even my question :) :

> mysql> select max(command_idx)  into @max_command_idx  from command;
> Query OK, 1 row affected (0.00 sec)

Now use the variable for the first time:

mysql> insert into command(controller_idx,subcontroller_idx,command_idx,controller_id, subcontroller_id,code,status,type,plan_name,timetable_id,offset,ctime,user_name,result) values
(0,0,@max_command_idx+1,'937','SUB0','SMS',0,'CONFIG','NA','NA',0,NOW(),'admin',0);
Query OK, 1 row affected (0.00 sec)

Now use the variable for the second time for another table:

> mysql> insert into
> csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,phone)
> values(0,0,@max_command_idx+1,0,'[phone]');ERROR 1048 (23000):
> Column 'command_idx' cannot be null

.......................................................................

My QUESTION: would you kindly tell me why I get this error?

THANK YOU .......................................................................

more information on my tables:

mysql> desc csmslist;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| controller_idx    | int(11)     | NO   | PRI | NULL    |       |
| subcontroller_idx | int(11)     | NO   | PRI | NULL    |       |
| command_idx       | int(11)     | NO   | PRI | NULL    |       |
| csmslist_idx      | int(11)     | NO   | PRI | NULL    |       |
| phone             | varchar(24) | YES  |     | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> desc command; 
+-------------------+-------------+------+-----+-------------------+-----------------------------+
| Field             | Type        | Null | Key | Default           | Extra                       |
+-------------------+-------------+------+-----+-------------------+-----------------------------+
| controller_idx    | int(11)     | NO   | PRI | NULL              |                             |
| subcontroller_idx | int(11)     | NO   | PRI | NULL              |                             |
| command_idx       | int(11)     | NO   | PRI | NULL              | auto_increment              |
| controller_id     | varchar(24) | YES  |     | NULL              |                             |
| subcontroller_id  | varchar(24) | YES  |     | NULL              |                             |
| code              | varchar(12) | YES  |     | NULL              |                             |
| status            | int(11)     | YES  |     | NULL              |                             |
| type              | varchar(24) | YES  |     | NULL              |                             |
| plan_name         | varchar(24) | YES  |     | NULL              |                             |
| timetable_id      | varchar(24) | YES  |     | NULL              |                             |
| offset            | int(11)     | YES  |     | NULL              |                             |
| ctime             | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_name         | varchar(80) | YES  |     | NULL              |                             |
| result            | int(11)     | YES  |     | NULL              |                             |
+-------------------+-------------+------+-----+-------------------+-----------------------------+
14 rows in set (0.01 sec)

解决方案

The command table has an auto_increment, when you set the column to null (which is what you are doing, despite what you think you are doing), it just does an auto increment on it.

The csmslist table has no auto increment so inserting null fails.

Do this instead:

insert into csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,phone)
values(0,0,LAST_INSERT_ID(),0,'[phone]');ERROR 1048 (23000):
Column 'command_idx' cannot be null

And in the insert to command just make the column NULL and let the database do its thing for you automatically.

It could be the command table is empty, so it's returning NULL for the max.

BTW instead of using a variable you can put that query right in there:

insert into csmslist(controller_idx,subcontroller_idx,command_idx,csmslist_idx,phone)
values(0,0,(select max(command_idx) from command),0,'[phone]');ERROR 1048 (23000):
Column 'command_idx' cannot be null

这篇关于mysql 脚本变量和 max function-2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！