Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 4

Displaying posts with tag: SQL Statements (reset)

Generating test data from the mysql> prompt
+0 Vote Up -0Vote Down

There are a lot of tools that generate test data.  Many of them have complex XML scripts or GUI interfaces that let you identify characteristics about the data. For testing query performance and many other applications, however, a simple quick and dirty data generator which can be constructed at the MySQL command line is useful.

First, let’s talk about what kind of data you can easily create with MySQL function calls:

You can generate a decimal number between zero and another number using the MySQL RAND() function like the following query (here between 0 and 10000):

SELECT RAND() * 10000;

Similarly, you can generate a random integer by adding the FLOOR() function:

SELECT FLOOR(RAND() * 10000)

You can generate a random string of 32 characters using MD5():

SELECT MD5(RAND() * 10000)

You can

  [Read more...]
Book Review – Effective MySQL
+3 Vote Up -0Vote Down

Read the original article at Book Review – Effective MySQL

Effective MySQL: Optimizing SQL Statements by Ronald Bradford No Nonsense, Readable, Practical, and Compact I like that this book is small; 150 pages means you can carry it easily.  It’s also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster [...]

For more articles like these go to iHeavy, Inc +1-212-533-6828

Keeping save – Injection attacks etc.
+1 Vote Up -0Vote Down

I had someone point out to me that some of the sample code I have used might be vulnerable to an insertion attack. So I feel it is time to address this subject.

First when using examples I try to just focus on the subject of the current blog and often strip a lot of things that are irrelevant to the point I am trying to make out. But now to the subject of keeping your code save.

If you write and SQL statement looking something like

lc_sql = “SELECT * from customer WHERE custno = ‘” + lc_custno + “‘”

Someone could enter “‘; TRUNCATE TABLE customer;’” resulting in  a SQL statement looking like

SELECT * from customer WHERE custno = ”; TRUNCATE TABLE customer; ”

Which could be quite disasterous. So to avoid this we could use parameterized queries which I cannot as I am using a seperate function to

  [Read more...]
Converting and optimizing SQL Statements
+0 Vote Up -0Vote Down

Now it became time to conveert some SQL statements. Even though many SELECT SQL statements will run unchanged in MYSQL it might be wise to check and see if they are performing well. There are some differences in how VFP and MYSQL optimize queries and a query that just performs great in VFP might be very slow in MYSQL. And then there are some cases where we have to replace things that are possible in VFP but not supported in MYSQL.

First lets assume that our tables all have lots of records. After all it does not pay to spend time on optimizing on tables with a few dozend records. I also will not go into VFP optimization here as this is a MYSQL blog.

Lets assume for our first case we might have a table with invoice detail records and a table with items that are on sale. So now we want to get maybe 2 queries. One to show a total of all the items that were on sale and the second a total of all the

  [Read more...]
Showing entries 1 to 4

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.