how to create unique key for long varchar?
hi all I want to create a table with a long varchar column, maybe it's the url. according to dns spec, the url's max length is fixed. but I have to deal with url having long params such as a.html?q=&fl= I want the url is unique when inserting it. I googled and found http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql this post suggests use md5 of url. But in theory, there will be conflict that two different urls will have the same md5(even it's probablitiy is very small). I want to a absolute correct solution. one method i can come up with is using select ... for update 1. begin transaction 2. select url from tb where md5='' for update 3. if the url is not exist, insert into this url; else do nothing also I need to set the transaction isolation to SERIALIZABLE is this solution correct in multithread/process environment? another method is using trigger to check whether the url exist. I don't know whether this method will be faster than previous one. how to ensure it's correctness in multithread environment? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
Am 04.11.2013 22:55, schrieb h...@tbbs.net: > 2013/11/04 09:32 -0800, Jan Steinman > I noticed that I have similar queries that work as expected. The difference > appears to be that every query that is broken uses " WITH ROLLUP", and > removing this makes them behave as expected. > > Is this a known bug? Should I submit it as such? > > If someone would be so kind as to point me to the bug system, I'll crawl > around in there to see if it's a known problem. > > There is a bug that I about a half year ago reported, > http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with "WITH > ROLLUP" is not always NULL, but is instead the foregoing string in the same > field. I suspect that other bugs with missing NULL found by searching for > ROLLUP are the same problem. (note link titled "Affects Me"!) > > Another, one year ago reported, bug of mine was handled in 5.7.2, but this > one not would you please quote in a readable way instead > signature.asc Description: OpenPGP digital signature
Re: Another query question...
2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses " WITH ROLLUP", and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with "WITH ROLLUP" is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled "Affects Me"!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Another query question...
MySQL 5.0.92-log I'm trying to form a clickable link using CONCAT, but the link as displayed points to the NEXT row's URL, not the one from the same row as the other data displayed! Is there something I don't understand about this? Below is the query. "{{{1}}}" is replaced by a year, like "2013". The second column is the problem one. When the table is displayed, the link in the `Product` field points to the NEXT SEQUENTIAL product row! In other words, if you click on the link for "garlic," you'll get the page for "gherkins." Live example is at: http://www.EcoReality.org/wiki/2013_harvest If you hover over the link in the `Product` column, you can clearly see that the page at the link is not the same as that in the `ID` column, but is in fact the same `ID` as the next sequential row. I am so confused. SELECT harvest.product AS ID, CONCAT('http://www.EcoReality.org/wiki/Product/', s_product.ID, '">', COALESCE(s_product.name, 'TOTAL:'), '') AS `Product`, FORMAT(sum(harvest.quantity), 3) AS `http://www.EcoReality.org/wiki/Harvest";>Harvest`, harvest.units AS Units, CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value, prices.market_type AS `R-W`, COUNT(*) AS Harvests, DATE(MIN(harvest.date)) AS Begin, DATE(MAX(harvest.date)) AS End FROM s_product_harvest harvest INNER JOIN s_product on s_product.ID = harvest.product AND s_product.units = harvest.units LEFT OUTER JOIN s_product_market_prices prices ON prices.product_ID = harvest.product AND prices.units = harvest.units AND year(prices.price_date) = year(harvest.date) WHERE year(harvest.date) = {{{1}}} GROUP BY s_product.name WITH ROLLUP Some days I wonder if it might not be better to culturally engineer humans to enjoy small scale garden farming than to genetically engineer weeds to save large scale agribusiness. -- Gene Logsdon Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
The plot thickens... I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses " WITH ROLLUP", and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. Jan Begin forwarded message: > From: Jan Steinman > Date: 3 November 2013 18:35:47 PST > > MySQL 5.0.92-log > > I'm trying to form a clickable link using CONCAT, but the link as displayed > points to the NEXT row's URL, not the one from the same row as the other data > displayed! > > Is there something I don't understand about this? > > Below is the query. "{{{1}}}" is replaced by a year, like "2013". > > The second column is the problem one. When the table is displayed, the link > in the `Product` field points to the NEXT SEQUENTIAL product row! In other > words, if you click on the link for "garlic," you'll get the page for > "gherkins." > > Live example is at: http://www.EcoReality.org/wiki/2013_harvest > > If you hover over the link in the `Product` column, you can clearly see that > the page at the link is not the same as that in the `ID` column, but is in > fact the same `ID` as the next sequential row. I am so confused. > > SELECT > harvest.product AS ID, > CONCAT('http://www.EcoReality.org/wiki/Product/', s_product.ID, > '">', COALESCE(s_product.name, 'TOTAL:'), '') AS `Product`, > FORMAT(sum(harvest.quantity), 3) AS ` href="http://www.EcoReality.org/wiki/Harvest";>Harvest`, > harvest.units AS Units, > CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value, > prices.market_type AS `R-W`, > COUNT(*) AS Harvests, > DATE(MIN(harvest.date)) AS Begin, > DATE(MAX(harvest.date)) AS End > FROM > s_product_harvest harvest > INNER JOIN > s_product on s_product.ID = harvest.product AND > s_product.units = harvest.units > LEFT OUTER JOIN > s_product_market_prices prices ON prices.product_ID = harvest.product AND > prices.units = harvest.units AND > year(prices.price_date) = year(harvest.date) > WHERE > year(harvest.date) = {{{1}}} > GROUP BY > s_product.name WITH ROLLUP > > Some days I wonder if it might not be better to culturally engineer > humans to enjoy small scale garden farming than to genetically engineer weeds > to save large scale agribusiness. -- Gene Logsdon > Jan Steinman, EcoReality Co-op > The competition for grain between the wealthy car drivers of the world and the poorest people who are trying to survive is a moral issue that we should not ignore. The continued increase in biofuels production will result in a continued decrease in food availability, which we could someday consider to be a crime against humanity. -- Pat Murphy Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Pivot Query Help
I'm using MySQL 5.0.92-log. I'm trying to do a pivot-sort-of-thing. I've tried a few things from the O'Reilly "SQL Cookbook," but I seem to be having a mental block. I have a table of farm harvests. Each harvest has a date, quantity, and foreign keys into product and harvester tables: CREATE TABLE s_product_harvest ( id int(10) unsigned NOT NULL auto_increment, `date` datetime NOT NULL COMMENT 'Date and time of harvest.', product int(11) unsigned NOT NULL default '53', quantity decimal(10,3) NOT NULL default '1.000', units enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') character set utf8 NOT NULL default 'kilograms', who1 int(5) unsigned NOT NULL default '2' COMMENT 'Who harvested this resource?', notes varchar(255) character set utf8 NOT NULL, PRIMARY KEY (id), KEY product (product), KEY `date` (`date`), KEY who1 (who1), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='historical list of EcoReality farm products harvested'; What I want is a report with years as columns, and rows of: first harvest (MIN(date)), last harvest (MAX(date)), days of harvest (DATEDIFF(MAX(date), MIN(date))) and total (SUM(quantity)). first/last 200720082009... first Aug 5 Sep 27 Aug 7 lastOct 1 Nov 24 Oct 16 days57 108 82 kg 10.17 16.746.53 This is my first attempt, and it appears to be giving me a row per year, with the first sequential harvest date for each year. I can get the data I want by making each one a separate column, but that's ugly and I want them in rows. SELECT 'first_last' AS `First/Last`, CASE WHEN YEAR(harvest.date)='2007' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2007', CASE WHEN YEAR(harvest.date)='2008' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2008', CASE WHEN YEAR(harvest.date)='2009' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2009', CASE WHEN YEAR(harvest.date)='2010' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2010', CASE WHEN YEAR(harvest.date)='2011' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2011', CASE WHEN YEAR(harvest.date)='2012' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2012', CASE WHEN YEAR(harvest.date)='2013' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2013', CASE WHEN YEAR(harvest.date)='2014' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2014' FROM s_product_harvest harvest WHERE harvest.product = 4 /* product ID for tomatoes */ GROUP BY YEAR(harvest.date) Using an example from "SQL Cookbook" on page 372, I tried to select from a subquery, grouped by a rank, but I kept getting one result row, and I can't figure out how to get the literal row headers. Any ideas? Compared to those on pasteurized milk, children who received raw certified milk had better weight gain and greater protection against rachitis. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql