MySQL: Resetting auto-increment value of a table

During development, usually we want to delete records from the table manually, then the tables auto-increment column remains where it was. We might want to reset the auto-increment value for the table.

For example, if you delete records with id's 7,8,9
Now when you insert through application, it inserts the new record starting at 10. But we want it to start at 7.

Use the following SQL to reset to a specific value

 ALTER TABLE my_table AUTO_INCREMENT=7  

If you want to delete all records from your table and restart auto-index at 1, then

 TRUNCATE TABLE my_table 

MySQL: Calculate DATE, TIME difference in SQL statement using MySQL DB

Following examples showing how to calculated DATE, TIME differences in SQL statement in MySQL database.

Functions used in the following examples are TIMEDIFF and DATEDIFF

 select TIMEDIFF('2011-10-31 12:50:56' , '2011-09-23 12:50:21')   
 The above outputs : 838:59:59  

 select TIMEDIFF('15:50:56' , '12:55:21')   
 The above SQL outputs: 02:55:35  

 select DATEDIFF('2011-10-31 12:50:56' , '2011-09-23 12:50:21')   
 The above SQL outputs: 38  

 select DATEDIFF('2011-10-31' , '2011-09-23')   
 The above SQL outputs: 38  

MySQL: Make a copy of an existing table in MySQL

We can create a copy of an existing table using the following SQL statements.

 CREATE TABLE booger_copy LIKE testing.booger;  
 INSERT booger_copy SELECT * FROM testing.booger;  

If you want to copy only half of the existing table then, you can use LIMIT keyword in the select Query

MySQL : Sort by Date and Time together when there are in different columns

Suppose you have date and time in two different columns of your table.
We can sort date and time together by using the ADDTIME function.

 select * from mytable ORDER BY ADDTIME(mydate, mytime);  

You can also use the ADDTIME in the select clause to get data and time together.

 select ADDTIME(mydate, mytime), city from mytable  

Mac OS X Fixing Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock'

How to fix Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock'

Apparently, Mac OS X doesn't allow for PHP MySQL connection out of the box. You get this:

    Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock'
OR In CakePHP
Warning: Could not connect to Database.

I found the most clear fix here:

    /etc/php.ini(.default) looks for mysql.sock in the wrong place... two options are to make a symbolic link from the right place to the socket...

    sudo mkdir /var/mysql
    sudo ln -s /private/tmp/mysql.sock /var/mysql/mysql.sock

    Or you can update your php.ini (.default) by finding "mysql.default_socket" and setting it to equal /private/tmp/mysql.sock and then restart apache web server