Removing Chinese or non ASCII Characters from the end of a string in MySQL

Step 1. First make sure the string has Chinese or non ASCII characters in it:

select asciiname from table1
where asciiname regexp(concat('[',char(128),'-',char(255),']'));


Step 2. If the Chinese name is definitely at the end of your field, you just need to trim the last occurrence of a space in your field. Confirm it is doing the right thing first:

select asciiname, reverse(substring(reverse(asciiname), locate(' ', reverse( asciiname ) )+1))
from table1
where asciiname regexp(concat('[',char(128),'-',char(255),']'));


Step 3. Execute:

update table1
set asciiname = reverse(substring(reverse(asciiname), locate(' ', reverse( asciiname ) )+1))
where asciiname regexp(concat('[',char(128),'-',char(255),']'));




My sources for this were (I am very grateful to):
1. http://stackoverflow.com/questions/461871/find-rows-with-non-ascii-values-in-a-column

2. http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Update from one mysql table to another

Something I did a lot of today. Updating from one table in mysql to another:


UPDATE table2 t2, table1 t1
SET t1.t2name = t2.name
WHERE
t1.id = t2.id

MySQL Create Table Syntax Example

Quick one:

CREATE TABLE mytable
(
myid INT NOT NULL AUTO_INCREMENT,
unique_name VARCHAR(255),
full_name VARCHAR(255),
phone_number VARCHAR(255),
loyalty_points INT,
PRIMARY KEY(myid,full_name)
);

MySQL for Mac OS X Snow Leopard

Simplified:

1. Go to here to get MySQL for Mac: http://dev.mysql.com/downloads/mysql/

2. Download the 32 bit .dmg archive. Click 'No thanks, just take me to the downloads!'

3. Run the archive and installer. (i.e. the mysql-5.x.xx-osx10.6-x86.pkg)

4. Set your root password by opening terminal and entering:

cd /usr/local/mysql/bin
sudo ./mysqladmin -u root password mysecretpassword

(I couldn't figure out the default password, nor was I asked to enter one during the installation)

5. Login and test it.

./mysql -u root -p

6. Open the dmg file you downloaded in step #2. Run the MySQLStartupItem.pkg.

7. Download MySQLWorkbench from http://dev.mysql.com/downloads/workbench/5.2.html if you need a GUI.

PHP5/Apache mysql.so in ubuntu

For my own reference:

1. apt-get install php5-mysql

2. sudo gedit /etc/php5/apache2/php.ini

3. add "extension=mysql.so"

Zen cart 1064 TYPE=MyISAM Problem

If you're getting the following error when trying to install Zencart:

"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 'TYPE=MyISAM' at line 1"

It is likely that you are trying to install on a relatively old MySQL version. To get around this problem:

1) find and open the file "zc_install\sql\mysql_zencart.sql"

2) Replace all instances of "TYPE=MyISAM" with "ENGINE=MyISAM"


That should help solve the problem.

Updating a table in SQL where records in table A don't exist in table B.

I find myself doing this a lot recently, so I'm posting it here too. Hopefully it is SQL92 and should run on just about anything.


1. Updating a table in SQL where records in table A don't exist in table B:

insert into tableB (field1, field2, field3)
select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like '%bleh%'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2);



2. Hey, and why not do something silly like have a union query and some limit in the mix (limit works in MySQL only):

insert into tableB (field1, field2, field3)
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A1
union
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 not like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A2;

MySQL: Updating from the same table

You might ask 'why' I am doing this, but that's not the point. To update a field in a table in mysql, from the same table (which MySQL disallows):

UPDATE mytable
set myvalue = myvalue+(select myvalue FROM (select myvalue from mytable where year='2008') b )
where
year = 2009


Basically, you have to trick mysql into selecting the subquery from a temporary table; as done above.

Kudos to Peter Geer for the idea for this.

MySQL, Insert Statement "ERROR 1064 (42000): You have an error in your SQL syntax"

So, I got the error "ERROR 1064 (42000): You have an error in your SQL syntax" when trying to execute a similar insert statement to:

INSERT INTO sections (code, desc) VALUES('123','example of a description');

It was due to the 'desc' field being a reserved word. If you're getting a similar error, make sure that your field names are not using any reserved words in MySQL. Although, yes it is kind of strange that MySQL would allow you to create fields based on reserved words to begin with. To solve my problem, I used:

INSERT INTO sections (code, description) VALUES('123','example of a description');

Resetting MySQL Root Password

Resetting the root password for MySQL can be done by using the following commands:


1: use mysql;
2: UPDATE user SET Password = PASSWORD("newpassword") WHERE user = 'root';
3: flush privileges;


(Tested on MySQL 5.1.11)