How do you set a default value for a MySQL Datetime column?
In SQL Server it's getdate(), what is the equivalant for MySQL? I'm using 5.x if that is a factor.
I am trying to run an UPDATE query
UPDATE ner.nerdataset, ner.shortabstracts
SET ner.nerdataset.ShortAbstract=ner.shortabstracts.ShortAbstract
WHERE ner.shortabstracts.Resource=ner.nerdataset.ResourceURI
LIMIT 10;
As MySQL does not allow the use of LIMIT when more than one table is involved in UPDATE what else can be done to limit this?
I use this mysql statement for concating description which has a length greater than 30..
select if (CHAR_LENGTH(description)>30,CONCAT(SUBSTRING(description,1,30),
'.....'),description) as description from table
How to change this mysql select into sql server select statement?
I'm thinking about moving from MySQL to Postgres for Rails development and I just want to hear what other developers that made the move have to say about it.
I'm looking for personal experiences, not a Mysql v Postgres shootout, just the pros and cons that you yourself have arrived at. Stuff that folks might not necessarily think.
Feel free to explain why you moved in the first place as well.
Which files are the actual files holding the data in the tables in a mysql db?
I went to /var/lib/mysql/ and I can see there a bunch of relatively small directories corresponding to my tables, and a lot of files named servername-bin.0000001 etc.
Is the data stored in those files?
I'm writing a script to parse some text files, and insert the data that they contain into a mysql database. I don't have root access on the server that this script will run on. I've been looking at mysql-python, but it requires a bunch of dependencies that I don't have available. Is there a simpler way to do this?
Imagine a have a table with a column named status:
status
------
A
A
A
B
C
C
D
D
D
How can I count how many rows have A, how many rows have B etc?
this kind of output:
A |B |C |D |E
------------------
3 |1 |2 |3 |0
As for E = O , this will always be A,B,C,D and E
Output should be one row (thus 1 query).
When doing a distinct count (most returning answer on my searches, it does return how many different elements there are, 4 in this case...)
Hi,
We building big Web Application and we use mysql, we want to make mysql database more fast.
Some of us think if we will put message html body inside table and not inside text.txt in will make database heavy and not fast.
Thanks,
*Part of main table that hold message:
option 1:hold html message body inside database
message {
id (int)
subject (varchar)
body (text)
}
option 2: hold html message body inside body1.txt file
message {
id (int)
subject (varchar)
file_body_path (varchar)
}
*
My classifieds website uses mainly PHP and MySql.
On error, (for example if a variable isn't found), I would like to have an error-page to show, is this possible? And I mean for every error to point to the same error-page.
I am thinking about htaccess, but maybe there are other ways also?
Same with MySql, how is it done there?
Thanks
A MySQL UPDATE clause removes slashes in values, although double slashes ensures that the values save correctly. Any way to disable the sensitivity to slashes? Can I disable it in my connector? I'm using the official connector "MySql.Data" for C#.
I am aware of the MySQL Date_Format function but am looking to achieve the following:
I have on column with a day date in 2 digit format (01-30). I am trying to update another date formatted field with the current year, the next month (m+1) and the day field mentioned previously.
In PHP i would do this using mktime function but this must be done using mysql calls only.
Is it possible to transform in this way?
What is the easiest way to activate PHP and MySQL on Mac OS 10.6 (Snow Leopard)?
I'd prefer to use any built in versions before resorting to downloading from the PHP or MySQL websites.
I'm comfortable at the command line in Terminal.
I have two tables, users and reports. Each user has no, one, or multiple reports associated with it, and the reports table has a user_id field.
I have the following query, and I need to add to each row a count of how many reports the user has:
SELECT *
FROM users
LIMIT 1, 10
Do I need to use a subquery, and if so, how can I use it efficently? The reports table has thousands and thousands of rows.
A process running on my machine collects data from various websites and stores it in the local mysql db. Same data is exported using SELECT INTO OUTFILE and FTPed to the shared host every few hours. My hosting provider doesn't allow LOAD DATA INFILE to be executed on the shared host? What are my other options for automated/scheduled load to MYSQL db on my shared host?
Hello Guys,
We have been using PHP/MySQL for our web application which has been growing a lot, the database is around 4-5GB and one of the table is 2GB sometimes, hence slowing down whenever any queries to that table is called.
Should we just try to optimize, or are we using MySQL above its limit? Will switching our web app to .NET/MSSQL resolve the issues?
Thanks
I somehow need this feature,but MySQL doesn't support it at this moment.
I'm using GROUP_CONCAT(CONCAT(...)) to generate a xml-like stuff.
But when the size exceeds the limit,the xml is just broken!
So I have to somehow make it only retrieve 5 rows !
I have a download.php file that connects to a remote mysql database. If I run it using
php download.php
it works fine. But if I create another php file form.php and then submit the form to this download.php, it complains the following:
Can't connect to MySQL server on '<IP_ADDRESS>' (13)
Does anyone know why this might be happening? I can't see a reason why this works directly but fails to work upon form submission...
I have a script that has a GET variable: $_GET['percentage']
I have a MySQL table of data.
Now lets say that there are 100 rows of data in this table.
In pseudo-code:
SELECT data FROM table
Now would it be possible to select $_GET['percentage'] of random data from table?
For example (again in pseudo-code):
$_GET['percentage'] = 10;
SELECT 10% of data from table order by rand()
If this IS possible, how could I do it?
I need to be able to write a script to automatically connect mysql in batch mode so that I can run some .sql files.
I tried to enter the following at the prompt:
./mysql -u root -p mypassword
but I keep getting a "Enter password: " prompt.
What am I doing incorrectly?
Thanks.
Hi all,
i am executing a procedure in mysql, procedure is-
delimiter $$
create procedure getFacility (in id int, out MyFacility VARCHAR(200))
begin
select Facility into MyFacility
from facilities
where FacilityID = id ;
end $$
delimiter ;
and it is giving error below-
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end $$
delimiter' at line 1
(0 ms taken)
how to solve this error?
Thanks,
Yugal
I see i can get a MySQL database defined encoding in PHP using the function mysql_client_encoding(...), but is there a way to get a MySQL table defined encoding in PHP ?
I've been doing a lot of calculating stuff nowadays.Usually I prefer to do this job in PHP rather than Mysql though I know PHP is not good at this cuz I thought mysql may be worse.But I found some performance problem :some pages were loaded so slowly that 30 seconds' timelimit is not enough for them!So I wonder which is the better practice to do the calculations,and any princles for that?Suggestions would be appreciated.
As I am a MySQL newbie. What does PARTITION mean in this MySQL statement?
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
We have a MySQL database and would like to have row-level security implemented at the database level. I have been playing with the Veil plug-in for PostgreSQL and like what it does. Is there something similar for MySQL so we do not have to convert over to PostgreSQL?