Consequences for not using semicolons at the end of mysql queries in PDO?

Question

Consequences for not using semicolons at the end of mysql queries in PDO?

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I had some data mixes in my mysql table and I'm thinking if that would be due to lack of some semicolons at the end of my queries. Let's say I have this code:

$sql = "DELETE FROM customers_2016 WHERE customer_db_id = ? ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $customer_db_id,
        )
    );

$sql = "UPDATE customers_2016
SET valor = 1
WHERE customer_db_id = ? ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $customer_db_id,
        )
    );

Could I get errors if I executed that code without semicolons at the end of the query hundreds of times each day?

mysql pdo

asked by Lukas 29.03.2017 в 16:28

source

2 answers

2

In php they are not necessary since it is used to execute multiple queries and in php that is not possible with a single execute, in fact if you put ...

$sql = "select * from usuario; update usuario set campo = 3;"

You are not going to execute both, you must put what mixture happened to you to see what the problem may be.

answered by 29.03.2017 в 16:57

Error connecting Java with SQL Difference between JDBC and JSON [closed]

score 1 · Accepted Answer

To answer your question: The semicolon is part of the MySQL query definition :

A query normally consists of an SQL statement followed by a semicolon.   (There are some exceptions where a semicolon may be omitted.   mentioned earlier, is one of them. We'll get to others later.)

A query usually consists of a SQL statement followed by a   semicolon. (There are some exceptions where you can omit a   semicolon, QUIT, mentioned above, is one of them.

It is possible that in PHP you will not have problems if you omit the ; at the end of the queries, because in the case of your example you send prepared queries that separate the SQL statement from the data they send.

I suppose, and, it would be interesting to investigate, that PDO or MySQLi, in case you omit the ; in the SQL string that you send, they provide it in your place.

But, I would never delegate to the program something I should do. Why build a query without relying on the rules set by the database manager I'm using? I can not in that sense be an adventurous programmer, much less if it is a data management ( UPDATE... DELETE ). Although the possibility of error omitting the ; is minimal or none, in this case ... who knows, you can take an unpleasant surprise.

If we consider the same command line usage we will quickly see that there could be problems omitting ; , affecting MySQL performance or causing some other error.

The following is taken from the MySQL documentation :

Multiple-line statements commonly occur by accident when you intend to issue a query on a single line, but forget the terminating semicolon. In this case, mysql waits for more input:
mysql> SELECT USER()
-> 
If this happens to you (you think you've entered a statement but the only response is a -> prompt), most likely mysql is waiting for the semicolon. If you do not notice what the prompt is telling you, you might sit there for a while before realizing what you need to do. Enter semicolon to complete the statement, and mysql executes it:

Sentences of several lines commonly occur by accident when you intend to issue a query on a single line, but forgets the final semicolon. In this case, mysql expects more Information :
mysql> SELECT USER()
-> 
If this happens to you ( you think you have entered a statement, but the only answer is a prompt -> ), it's likely that mysql is waiting for the semicolon . If you do not notice what you are saying, you can sit there for a while before doing what you need to do. Enter a semicolon to complete the statement and mysql executes it.

Conclusion

Semicolon always in queries that require it ( SELECT... UPDATE... DELETE... INSERT... and others)

There is no excuse for not using it.