MySQL

mysqldump data only

Also you may use: –skip-triggers: if you are using triggers –no-create-db: if you are using –databases … option –compact: if you want to get rid of extra comments

MySQL: Cloning a MySQL database on the same MySql instance

As the manual says in Copying Databases you can pipe the dump directly into the mysql client: If you’re using MyISAM you could copy the files, but I wouldn’t recommend it. It’s a bit dodgy. Integrated from various good other answers Both mysqldump and mysql commands accept options for setting connection details (and much more), like: Also, if the new database is not … Read more

CAST to DECIMAL in MySQL

From MySQL docs: Fixed-Point Types (Exact Value) – DECIMAL, NUMERIC: In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0) So, you are converting to a number with 2 integer digits and 0 decimal digits. Try this instead:

Differences between utf8 and latin1

UTF-8 is prepared for world domination, Latin1 isn’t. If you’re trying to store non-Latin characters like Chinese, Japanese, Hebrew, Russian, etc using Latin1 encoding, then they will end up as mojibake. You may find the introductory text of this article useful (and even more if you know a bit Java). Note that full 4-byte UTF-8 support was only introduced in MySQL … Read more

select * from table where column = something or, when unavailable, column = something else

You can provide a custom ordering, then take the first row, like this: Explanation: The inner select’s order by field(LOCALE, ‘ge’, ‘_’) gets you the rows in the order you define – in this case German first if it exists, then English (you could add more languages to the list). The “trick” here is using mysql’s “non-standard” GROUP … Read more

Drop all databases in MySQL

We have many databases in our MySQL and we want to shrink/purge ibdata1 file in MySQL. How can we drop all the databases from the MySQL except information_schema and mysqld databases?

Remove Primary Key in MySQL

Without an index, maintaining an autoincrement column becomes too expensive, that’s why MySQL requires an autoincrement column to be a leftmost part of an index. You should remove the autoincrement property before dropping the key: Note that you have a composite PRIMARY KEY which covers all three columns and id is not guaranteed to be … Read more

Node.js MySQL – Error: connect ECONNREFUSED

I use Node.js server side. I tried my code on localhost and everything works fine. I bought a server and installed Apache and node.js on it and test my web application there. I correctly changed the MySQL connection configurations from localhost to the server configurations. I test my web application with this configuration: I start … Read more

+ More