Use INSERT IGNORE INTO table. There’s also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement. Post from bogdan.org.ua according to Google’s webcache: 18th October 2007 To start: as of the latest MySQL, syntax presented in the title is not possible. But there … Read more
Sub-queries are the logically correct way to solve problems of the form, “Get facts from A, conditional on facts from B”. In such instances, it makes more logical sense to stick B in a sub-query than to do a join. It is also safer, in a practical sense, since you don’t have to be cautious … Read more
INNER JOIN is ANSI syntax that you should use. It is generally considered more readable, especially when you join lots of tables. It can also be easily replaced with an OUTER JOIN whenever a need arises. The WHERE syntax is more relational model oriented. A result of two tables JOINed is a cartesian product of … Read more
You can use this query to show the size of a table (although you need to substitute the variables first): SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = “$DB_NAME” AND table_name = “$TABLE_NAME”; or this query to list the size of every table … Read more
Use INSERT … ON DUPLICATE KEY UPDATE QUERY: INSERT INTO table (id, name, age) VALUES(1, “A”, 19) ON DUPLICATE KEY UPDATE name=”A”, age=19
It looks like your MySql session has the safe-updates option set. This means that you can’t update or delete records without specifying a key (ex. primary key) in the where clause. Try: SET SQL_SAFE_UPDATES = 0; Or you can modify your query to follow the rule (use primary key in where clause).
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: RENAME TABLE old_db.table TO new_db.table; You will need to adjust the permissions after that. For scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe ‘show … Read more
MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; This and other approaches … Read more
For those people still arriving at this question in 2020 or later, there are newer options that may be better than both of these. For example, utf8_unicode_520_ci. All these collations are for the UTF-8 character encoding. The differences are in how text is sorted and compared. _unicode_ci and _general_ci are two different sets of rules … Read more
Connecting to MYSQL with Python 2 in three steps 1 – Setting You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it’s hard to install it using easy_install. Please note MySQLdb only supports … Read more