What is the mysql equivalent of Sql Server's @@Identity and @@error and how to use try catch in mysql

I am looking for 开发者_运维问答few global variable in mysql which are equivalent of sql server.

I want the whole list eg.

Sql Server Equivalent

@@error ---

@@Identity ---

etc.

Basically right now I want to know what are the equivalent variable of @@error and @@identity.

But it will be helpfull if u could provide some other variable also

The last auto_increment value (i.e. the last identity) generated for the current connection can be found using the LAST_INSERT_ID() function.

About errors, not sure ; there doesn't seem to be any system variable that corresponds to the last error message.

There is a show errors statement :

mysql> select a from b;
ERROR 1046 (3D000): No database selected

mysql> show errors;
+-------+------+----------------------+
| Level | Code | Message              |
+-------+------+----------------------+
| Error | 1046 | No database selected | 
+-------+------+----------------------+
1 row in set (0,00 sec)

But not sure how you can use this result...

After searching a bit more, I found out this thread : Getting the last error message, which says (quoting) :

I think there should be something like @@last_error_id and @@last_error_message, but I can`t find anything in the current manual.

And the first answer states (quoting) :

This isn't possible currently, from what we understand error handling will be improved in version 5.2 and hopefully something like this may be possible.

If you are interested in the number of errors (or if there was an error), using @@error_count seems to work.

> select a from bogus_table;
(1054, "Unknown column 'a' in 'field list'")
> select @@error_count;
+---------------+
| @@error_count |
+---------------+
| 1             |
+---------------+

Regarding Errors, there is a facility using which you can get the error number and other things. There is a utility named GET DIAGNOSTICS using which you can query database diagnostics data. Refer the example below -

GET DIAGNOSTICS @NUM = NUMBER, @rowsAffected = ROW_COUNT; 
-- here @NUM and @rowsAffected are user variables. Any other variables can work here as well.

In above statement you can get number of error conditions and number of rows affected. For error conditions we refer another variant of this utility which consumes number of error count that we got from above statement.

GET DIAGNOSTICS CONDITION @NUM
@errNo = mysql_errno, -- Error Number
@msg = message_text, -- Error Message Text
@sqlState = sqlstate_returned -- Affected SQL State under which error occured
;

A word of caution though - use these statement as soon as you expect your sql to fail. Since these capture internals database diagnostics any other function can quickly overlap your session data.

For more on this, refer link here