如何在MySQL脚本中检查前面语句中的错误？

I am running some MySQL scripts (for batches) from shell scripts, just using the mysql command line utility. In one of these scripts, I want to create a new table, populate it, and when I am positive that the population succeeded, rename it to take the place of an old table, and drop the old table, so the sequence of events looks like this:

我正在使用mysql命令行实用程序从shell脚本运行一些MySQL脚本(用于批处理)。在其中一个脚本中,我想创建一个新表,填充它,当我肯定人口成功时,将其重命名为取代旧表,并删除旧表,因此事件序列看起来如此像这样:

create table if not exists mystuff_tmp (
  id int(10) unsigned not null auto_increment,
  average_value decimal(10,4) default null,
  day date not null,
  primary key (id)
) engine=InnoDB default charset=utf8;

insert into mystuff_tmp (
    average_value,
    day
) select
    avg(value),
    date(input)
from
    details
where
    input between date(now()) - interval 7 day and date(now)
group by
    date(input);

***if there were no errors***
rename table mystuff to mystuff_old, mystuff_tmp to mystuff;
drop table mystuff_old;

In Sybase (and its children, like MS SQL), there's @@error, but the only similar system variable I find in MySQL is error_count, and that does not get updated by session errors...

在Sybase(及其子代,如MS SQL)中,有@@错误,但我在MySQL中找到的唯一类似系统变量是error_count,并且不会因会话错误而更新...

3 个解决方案

#1

I think

SELECT @@session.warning_count;

SELECT @@session.error_count;

will get you what you want

会得到你想要的

#2

What I do is just break up the shell script into parts. For example:

我所做的只是将shell脚本分解成几部分。例如:

mysql_cmd="mysql -u$mysql_user -p$mysql_pw $db"
$mysql_cmd <<eot
create table if not exists mystuff_tmp (
  id int(10) unsigned not null auto_increment,
  average_value decimal(10,4) default null,
  day date not null,
  primary key (id)
) engine=InnoDB default charset=utf8;

insert into mystuff_tmp (
    average_value,
    day
) select
    avg(value),
    date(input)
from
    details
where
    input between date(now()) - interval 7 day and date(now)
group by
    date(input);
eot

if [ $? != 0]
then
  $mysql_cmd <<eot
rename table mystuff to mystuff_old, mystuff_tmp to mystuff;
drop table mystuff_old;
eot
fi

#3

Mysql has error handlers to handle various errors, and warnings. In these error handlers you can specify value to a variable. Before the rename operation you can check the value of the variable.

Mysql有错误处理程序来处理各种错误和警告。在这些错误处理程序中,您可以为变量指定值。在重命名操作之前,您可以检查变量的值。

#1