mysql – Make Me Engineer

How to transpose rows to columns with large amount of the data in BigQuery/SQL?

How to insert images from database?

Open the XML view of .jrxml file. Then find code similar to this <field name=”image” class=”java.lang.Object”/> /* “image” must be replaced by your actual field name */ and change it to <field name=”image” class=”java.io.InputStream”/>

Find date range overlaps within the same table, for specific user MySQL

Here is the first part: Overlapping cars per user… SQLFiddle – correlated Query and Join Query Second part – more than one user in one car at the same time: SQLFiddle – correlated Query and Join Query. Query below… I use the correlated queries: You will likely need indexes on userid and ‘car’. However – … Read more

MySQL Stored Procedures not working with SELECT (basic question)

Figured it out. This is not a bug with PHP (though it used to be) – it’s a bug in some versions of phpmyadmin. The same bug intermittently reappears and is then fixed in various subversions (see above): #1312 – PROCEDURE [name] can’t return a result set in the given context This behavior appears limited … Read more

Finding number position in string

With help of xdazz answer, I did some changes and got answer finally… SELECT myWord, LEAST ( if (Locate(‘0’,myWord) >0,Locate(‘0’,myWord),999), if (Locate(‘1’,myWord) >0,Locate(‘1’,myWord),999), if (Locate(‘2’,myWord) >0,Locate(‘2’,myWord),999), if (Locate(‘3’,myWord) >0,Locate(‘3’,myWord),999), if (Locate(‘4’,myWord) >0,Locate(‘4’,myWord),999), if (Locate(‘5’,myWord) >0,Locate(‘5’,myWord),999), if (Locate(‘6’,myWord) >0,Locate(‘6’,myWord),999), if (Locate(‘7’,myWord) >0,Locate(‘7’,myWord),999), if (Locate(‘8’,myWord) >0,Locate(‘8’,myWord),999), if (Locate(‘9’,myWord) >0,Locate(‘9’,myWord),999) ) as myPos FROM myTable; Demo

How to delete duplicate rows from a MySQL table

DELETE DupRows.* FROM MyTable AS DupRows INNER JOIN ( SELECT MIN(ID) AS minId, col1, col2 FROM MyTable GROUP BY col1, col2 HAVING COUNT(*) > 1 ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2 AND SaveRows.minId <> DupRows.ID; Of course you have to extend col1, col2 in all three places to all columns. … Read more

Using reserved words in column names

You can still use key if you want to. Just wrap it with backtick, CREATE TABLE IF NOT EXISTS users ( `key` INT PRIMARY KEY NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, ); but as an advise, refrain from using any reserved keyword to avoid future problems. 🙂 MySQL Reserved Keywords List

Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

I’m not sure why you want to ban JOIN as its fairly essential to SQL. It’s like banning function calls in a functional language. A good way to solve this is to create a result set containing the ids you want to return and join with it. Here’s one way to do it: SELECT Table1.* … Read more

MySQL LOAD_FILE() loads null values

I copied my file to the location where MySQL has access to. To know the location I used: select @@secure_file_priv; and it gave me /var/lib/mysql-files/. Nothing else worked: neither turning off apparmor, nor changing ownership and permissions, nor merely granting the file privilege. So I rolled back most of that but the right directory still … Read more

+ More