DELETE row question


#1

If I were to delete an entry in my table, what happens to the now empty spot? Will everything after that move up one or does it stay empty? If it were to move up and I had a number auto_inc, would all those values below this entry change or would there be a gap in the numbers?

Thank you


#2

There is a gap in the numbers and also a gap in the data files that actually make up the tables. Run the OPTIMIZE TABLE command on any table you think needs it. Generally this isn’t needed unless you do alot of deletes.


#3

One more question

If I have a DATE or DATETIME in one col of a table and I INSERT or UPDATE a row, will the DATE/DATETIME be submitted with a NULL command. I know this will happen if I have an int that auto_inc, but what about DATE/DATETIME? How do I add the date to this portion of my table?

Thank you


#4

with a date/datetime column I personally just create the string to go in there (note PHP’s date()) though you can also fill it with MySQL’s CURDATE() function.

References:
MySQL manual - http://dev.mysql.com/doc/mysql/en/Date_ … tions.html
PHP manual - http://www.php.net/manual/en/function.date.php


#5

If you would like the Date/Time field to update everytime you insert up data that record automatically just use a TIMESTAMP field.


#6

Yes, there will be a gap in the auto-increment numbers, but it’s not really a problem, unless you ever plan to have over 4 billion entries (the answer is no).

You don’t really have to care about how MySQL stores them internally.