Is there a property that I can add to a column so that it converts its value to lowercase? Instead of doing it for every single value through PHP? Answer You could probably do it through a trigger that fires on insert or update. Myself, I’d rather just create a view that has a lower-case version of the column in
Tag: mysql
MySQL OPTIMIZE all tables?
MySQL has an OPTIMIZE TABLE command which can be used to reclaim unused space in a MySQL install. Is there a way (built-in command or common stored procedure) to run this optimization for every table in the database and/or server install, or is this something you’d have to script up yourself? Answer You can use mysqlcheck to do this at
INSERT with SELECT
I have a query that inserts using a SELECT statement: Is it possible to only select “name, location” for the insert, and set gid to something else in the query? Answer Yes, absolutely, but check your syntax. You can put a constant of the same type as gid in its place, not just 1, of course. And, I just made
how to sort order of LEFT JOIN in SQL query?
OK I tried googling for an answer like crazy, but I couldn’t resolve this, so I hope someone will be able to help. Let’s say I have a table of users, very simple table: and I have another table of their cars and their prices. Now what I need to do is something like this (feel free to rewrite): Which
MySQL INSERT/UPDATE on POINT column
I’m trying to populate my DB with geographical places of my country. One of my tables have 4 fields: ID[PK], latitude. longitude ande geoPoint EDIT `SCDBs`.`Punto_Geografico`; SET @lat = 18.469692; …
Insert with Hibernate native query does not work for java.util.Date
I am using Hibernate JPA and Spring with a Mysql database and I want to insert using a SQL statement like this: But after running it I get the following error: Could someone help me on this please? PS. I am aware of using hibernate in non-native way, but I need to use native way. I am also of insert
MySQL query finding values in a comma separated string
I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors. When running the query select * from shirts where colors like ‘%1%’ to get all the red shirts (color=1), I also get the shirts whose color is grey (=12) and orange (=15). How should
Drop multiple tables in one shot in mysql
How to drop multiple tables from one single database at one command. something like, > use test; > drop table a,b,c; where a,b,c are the tables from database test.
Two Inner Joins MYSQL
How would I preform two inner joins in one query? Ie: three tables Invoice Address Client Invoice has a column which references an id in clients. It also has a column which references an address. I need to get both the clients name from the matched table and the address from the matched table. How would I INNER JOIN both
Conditional NOT NULL case SQL
I am trying to calculate a field and I want it to behave differently depending on if one of the columns happens to be null. I am using MySQL Is this the right syntax? Answer You need to have when reply.replies IS NOT NULL NULL is a special case in SQL and cannot be compared with = or <> operators.