Tag Archives: mysql

Omitting date completed from MySQL dump file

Posted on by

By default when you run a dump with ‘mysqldump’ the date of the dump is appended to the file, e.g.:

jj5@love:~/desktop/experiment$ udiff *
--- dbt__jj_dev_1__svn_jdrepo.1.sql     2019-06-11 18:11:13.267758230 +1000
+++ dbt__jj_dev_1__svn_jdrepo.2.sql     2019-06-11 18:12:03.856075974 +1000
@@ -32,4 +32,4 @@
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

--- Dump completed on 2019-06-10 21:59:44
+-- Dump completed on 2019-06-10 12:06:49

This causes dumps for a single database that has not changed to have two dumps which differ. It’s better to have dumps from the same unchanged database to be the same. To facilitate that add the –skip-dump-date option when running ‘mysqldump’.

See here for the back-story.

PDO: Execute a prepared statement using array for IN clause

Posted on by

See Example #5 here.

/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);

psql show tables

Posted on by
Reply

I wanted to know what the analogue for MySQL ‘show tables’ was in PostgreSQL, and I found the answer, use the information_schema, here:

mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

MySQL INET_ATON and INET_NTOA

Posted on by
Reply

You can convert an IP address to an int, and vice versa, with these two MySQL functions. Of course I learned about this after I’d already implemented IP address support using BINARY(4) and my own parser/formatter… now that I have my own implementation I can’t bring myself to let it go, and I worry about support for signed/unsigned 32-bit ints (perhaps my concerns are unfounded..?)